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!

=IF(I2="","No",IF(I2<=E2+TIME(1,0,0),"Yes","No"))

If this post helps click Yes
---------------
Jacob Skaria


"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!

This formula will work for you. If either I2 or E2 are blank, then you get
"No". Then this tests for hours differences and minutes differences. The
hour difference is allowed to be one if the minutes difference is zero. I
have used ABS (absolute value) in the date differences because it looks like
either date/time could be earlier.

=IF(I2="","No",IF(E2="","No",IF(HOUR(ABS(E2-I2))0,IF(HOUR(ABS(E2-I2))1,"No",IF(MINUTE(ABS(E2-I2))=0,"Yes","No")),IF(MINUTE(ABS(E2-I2))<=60,"Yes","No"))))

Try it out...

--
Daryl S


"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!