compare time values - current time with time in cell range?

I have a time in a cell that might be one of a few times, like 10:30 or 18:30 or 02:30. I need to write an if statement that returns true if the current time on the computer is within 2 hours of the time in the cell (so for 18:30, it returns true if its 16:30 to 18:29), otherwise just returns false. I'm getting confused with the logic cause i know i should be subtracting 2 hrs from the time in the cell, but having trouble with math operations on time values...

the cell will always be in the format 00:00 (military time?), any ideas how to do this?

Wyatt...

just to give an idea of how i'm trying to figure it out (in formula),

Please Login or Register  to view this content.

apparently you can't have a negative time value, so i'm going the other way, but like i said, it seems like i'm going in circles... but above gives error.

Wyatt...

i'm very close, but the logic is eluding me...seems =a1-2 just returns ###### so I need to figure out how to subtract 2 hours from value in A1 (-2 doesn't work)

Please Login or Register  to view this content.

Anyone have an idea? I'm tried searching but its usually examples subtracting 2 time values...

Ok, I got an AND statement working to return true/false:

Please Login or Register  to view this content.

I put 02:00 in I5 as a workaround since i couldn't subtract 02:00 directly. Now, I have a column of times that I want to check it against with conditional formatting (if return is true, a cell on same row is filled in yellow). The code above returns true, but how do I check against all cells in a given column?

I was able to do it with a countif:

Please Login or Register  to view this content.

which checked all rows in C against all rows in column I ($I7), but how would i check every time in a column and if true, highlight a cell in the same row 5 columns over?