Excel coding using if for dates

April 29, 2015 at 02:15:30
Specs: Windows 7
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..


See More: Excel coding using if for dates

Report •


#1
April 29, 2015 at 04:23:28
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 than the 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.


Report •

#2
April 29, 2015 at 17:57:30
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


Report •

#3
April 30, 2015 at 05:53:54
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_test is 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


Report •

Related Solutions

#4
May 1, 2015 at 18:59:47
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"))


Report •

#5
May 1, 2015 at 20:02:05
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 exactly what both of my formulas do.

You said:

Where A1 is a cell where the user input the requested date of delivery

Please put January 1, 2015 in A1

The you said:

while b1 a cell is where the user input the date when the item was delivered

Please 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 not greater 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 before the 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 number

Why 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 not saying 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.


Report •

#6
May 1, 2015 at 22:42:09
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.

Report •

Ask Question