HELP! I need to make an IF Statement comparing date/time f..

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!