Comparing time and date values

[reswan]

Trying to identify rows where there is a 2 hour or greater difference between the time and date in cols A&B and that of cols C&D

A simple No is required in col E where there is not a 2 hour or greater time difference.
A simple Yes is required in col E where there is a 2 hour or greater time difference.

Have manually populated col E with a few examples.

Thanks for any ideas you have.

[Keebellah]

Hi, I suggest you use a timestamp or create it
That way you can do a simple IF for a time difference greater or equal to 2 hours
You've got a time column and date for one and another for the other time and date

[MrShorty]

Assuming I understand correctly:

1) Combine date and time into a single date/time serial number. A1+B1, C1+D1
2) subtract the two date/time numbers (absolute value to avoid sign issues) ABS((A1+B1)-(C1+D1))
3) see if it is larger than 1/12 of a day (2 hours/24 hours/day) =ABS(...)>CONVERT(2,"hr","day")
4) This will put FALSE where the difference is less than or equal to 2 hours, and a TRUE where the difference is greater than 2 hours. To change to "yes"/"no", use an IF() function =IF(ABS(...)>CONVERT(...),"YES","NO")

I notice that this puts "no" in E3 (difference appears to be about 1.5 hours), but you have marked this one as "yes". Is this a mistake in your example, or did I misunderstand something?

[reswan]

MrShorty, followed those steps and it worked perfectly!! The E3 entry was my mistake.
Thanks so much, added to your reputation.

[Keebellah]

don't forget to mark the post as SOLVED