Good day i would like to ask how to make an if statement with dates it would appear like this

a1=any date depending on expected delivery

b1=date received

c1=either "Delayed" or "Ontime"IF A1<=b1 then c1="Ontime" else c1="Delayed" End

something like this for excel..

Here is what you asked for: IF A1<=b1 then c1="Ontime" else c1="Delayed" End

The syntax of an IF function is =IF(

logical-test, value-if-true, value-if-false)=IF(A1<=B1,"Ontime","Delayed")

However, I have a question.

If A1 is the expected delivery date, and it is

less thanthe date received, isn't the package Delayed?What am I missing?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Actually sir the question is kind of like this; Where A1 is a cell where the user input the requested date of delivery while b1 a cell is where the user input the date when the item was delivered and C1 is where i need to make an IF statement to show if the delivered item arrived exact or earlier than expected to show the word "Good" while if the item was delivered exceeding the assigned delivered date it would show a "Bad" status telling the user that the supplier did not the met conditions required.

So thats about it. Hoping for your help. Thanks

That is what I thought you meant, which means that you want B1 to be less than or equal to A1 for an Ontime status, not the other way around. Either one of these should work for you:

Option 1: If the Expected date is less than the Delivered date, then the item was Delayed.

=IF(A1<B1,"Delayed","Ontime")

Option 2: If the Expected date is greater than or equal to the Delivered date, then the item was Ontime.

=IF(A1>=B1,"Ontime","Delayed")

If you compare both of those options to the syntax of an IF function, you should be able to see why they both work. It is all based on whether or not the

logical_testis TRUE.=IF(

logical_test, value-if-true, value-if-false)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Actually sir its the other way around. If the delivery came earlier than the expected date it would show a status of "Good" or "Ontime" if equivalent while if exceeding the expected date it would prompt "Bad" or "Delayed". Also sir I actually tried the formula you provided before and it didn't work due to that the date canot be converted to a number but instead i tried another formula and it actually workout fine. =IF(M6=0,"No Delivery",IF(M6<=MEDIAN(L6:M6),"Good","Bad"))

re: If the delivery came earlier than the expected date it would show a status of "Good" or "Ontime" if equivalent while if exceeding the expected date it would prompt "Bad" or "Delayed".That is

exactlywhat both of my formulas do.You said:

Where A1 is a cell where the user input the requested date of deliveryPlease put January 1, 2015 in A1

The you said:

while b1 a cell is where the user input the date when the item was deliveredPlease put January 5, 2015 in B1

In that situation, A1 is before (less than) B1, correct? Therefore the item is delayed, correct?

Let's look at my first suggestion:

=IF(A1<B1,"Delayed","Ontime")

January 1 (the expected date in A1 ) is less than January 5 (the delivery date in B1) therefore the formula will return "Delayed"

Now let's look at my second suggestion:

=IF(A1>=B1,"Ontime","Delayed")

Using those same dates, this formula will also return "Delayed" because A1 (the expected date)

is notgreater than or equal to B1 (the delivered date).Now, if you swap those dates (A1=January 5, 2015 B1=January 1, 2015) both of my formulas will return "Ontime" since the item was delivered

beforethe expected date.Please explain to me, using examples like I did, why you think my formulas will not work.

You also said:

it didn't work due to that the date canot be converted to a numberWhy do you say that? All Dates (and Times) are converted to numbers internally by Excel. Because the Dates are stored as numbers deep within Excel, an IF statement can compare 2 Dates directly. Please read the information found here:

http://www.cpearson.com/excel/datet...

Based on what you have requested, there is no need to use the MEDIAN function. I am

notsaying that your formula doesn't work, I am merely saying that the MEDIAN function is totally unnecessary since a simple IF function will work. You are taking a very simple comparison of 2 values (e.g. Dates) and complicating it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you sir for your advise. The truth is i tried the formula before but it didn't, I was prompted with an invalid input, so that's when i tried the median, but after trying again the formula that you have given me sir, it finally worked. So now I'm wondering why it didn't work in the first place. But still, I really appreciate the effort of you teaching me this simple solution over a very simple problem that I turned into a much complicated problem. I learned a lot actually, thank you.

Ask Your Question

Weekly Poll