HELP! IF statements to compare Date/Time

Microsoft Excel 2003 (full)
November 4, 2009 at 07:51:09
Specs: Windows XP
I have been trying to use an IF statement to compare date/time entries in a report I am building (format = yyyy-mm-dd hh:mm:ss). Essentially there are a few comparisons I must make to return a YES or NO value. I am unable to get the last one working.

1. Comparing one date/time (F2) to see if it is within 45 minutes of another field (E2). I made another field to calculate the difference between E2 and F2 (=E2-F2) and left it in the format hh:mm. I created another field (H2) with the value of 00:45 (hh:mm). Essentially, I wanted the statement to return a value of NO if F2 is blank, a value of YES if the time difference is 45 minutes or less, and a value of NO if the time difference was more than 45 minutes. Here is the resulting statement:
=IF(F2="", "No", IF(G2>=H2, "Yes", "No"))

2. Comparing one date/time (I2) to see if it is within 15 minutes of another field (E2). I made another field to calculate the difference between E2 and I2 (=E2-I2) and left it in the format hh:mm. I created another field (K2) with the value of 00:15 (hh:mm). Essentially, I wanted the statement to return a value of NO if I2 is blank, a value of YES if the time difference is 15 minutes or less, and a value of NO if the time difference was more than 15 minutes. Here is the resulting statement:
=IF(I2="", "No", IF(J2>=K2, "Yes", "No"))

3. Lastly, I need to create an IF statement that will return a value of NO if the field I2 is blank, YES is if the date/time in I2 is less than or equal to E2+60 mins, and NO if the date/time in I2 is greater than E2+60 mins.

example:
I2 = 2009-11-04 09:15:00, E2 = 2009-11-04 10:00:00, YES
I2 = 2009-11-04 10:45:00, E2 = 2009-11-04 10:00:00, YES
I2 = 2009-11-04 11:00:00, E2 = 2009-11-04 10:00:00, YES
I2 = Blank, E2 = 2009-11-04 10:00:00, NO
I2 = 2009-11-04 11:01:00, E2 = 2009-11-04 10:00:00, NO


Can someone please advise if there is a better formula to use?

Thanks!


See More: HELP! IF statements to compare Date/Time

Report •


#1
November 4, 2009 at 15:33:17
Hi,

Regarding questions 1 & 2:
I was not clear if the formulas you gave (=IF(F2="", "No", IF(G2>=H2, "Yes", "No")) are working or not.

Times in Excel are stored as the decimal part of a number and dates are in the integer or whole number part. You can have unexpected results when a time is displayed but there is also date information which you can't see. This can result in unexpected results such as 0:45 is not equal to 0:45 (because they are on different dates).

To overcome this use modulus division (and divide by 1), to return only the decimal part of the value

You don't need the intermediate cells such as E2-F2 or a cell holding 0:45

As Excel times are fractions of a day (0.5 is 12 noon, i.e., half way through the day) you can create the 45 minute or 15 minute value right in the formula as follows:

=IF(F2="", "No", IF(MOD(E2,1)-MOD(F2,1)>=(45/(60*24)), "Yes", "No"))

If you want to include the date in your calculation, then you will need to include controls on date and time entry to be sure that the date part is what you expect. (using mod() removes the issue, but then you need a further tweak to the formula to handle time differences that span midnight).

The formula including any date difference is just:

=IF(F2="", "No", IF(E2-F2>=(45/(60*24)), "Yes", "No"))


I still have to look at question 3, but its time to fire up the BBQ, so it will have to be tomorrow, but someone else may have answered by then !

Regards


Report •

#2
November 5, 2009 at 06:30:09
Hi,

Regarding question 3

If you want to keep the date in the equation, i.e., 10:00AM on one day is different to 10:00AM on the next day, the following formula works with the data you provided:

=IF(I18="","NO",IF(ROUND(I18,8)>ROUND(E18+(60/(60*24)),8),"NO","YES"))

If you want to ignore the date, this should do it:

=IF(I18="","NO",IF(ROUND(MOD(I18,1),8)>ROUND(MOD(E18,1)+(60/(60*24)),8),"NO","YES"))

The reason for the ROUND() is because you wanted exactly 60 minutes to return "YES", and the last few decimal places of the time and the start time +60/(60*24) are very slightly different, so by excluding (actually rounding), the last few decimal places, allows 11:00 to equal 10:00+(60/(60*24))

Hope this helps

Regards


Report •

#3
November 5, 2009 at 08:50:57
Thank you, thank you, thank you!!!!! You have solved my problem. All of your solutions have worked.

Report •

Related Solutions

#4
November 5, 2009 at 09:06:44
You're welcome

Report •


Ask Question