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!

Date and time fields are just numbers. The way you display them is not
relevant to the calculations. Days are the number of days since 1/1900.
Today is 40121 days since 1/1/1900 so if you enter the formula =TODAY() in a
cell you can copy it and paste special values to another location and see
that the value is 40121. Time is a number between 0 and 1 which represents
the fraction of the day. At 12:00 noon a time field would have the value .5
(half the day) So to look for a difference of 45 minutes - that's a
difference of .03125

"Ksoloway" wrote:

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!