Conditional Formatting time comparison w/ =NOW()

Guy's and Gals,

I was wondering if you could help me. I'm not sure if it's possible, but I want to be able to highlight a whole row of data based on whether a plane has taken off and whether it has landed. I'm a total newb when it comes to excel.

So far I've identified 3 conditions that need to be met.

[condition I]
If the current time lies between arrival and departure time, high light the row of data 'green'

[condition II]
if the current time falls after the arrival, highlight the row of data 'red'

[condition III]
if no other condition is met, leave color as is

You're help is greatly appreciated.


Also: How would I go about setting up conditional formatting where another user w/no Excel experience can edit without worrying about corrupting my functions?

hi khamplex,

it's very hard to handle times in excel that are not stored as times.
But here I've try:

Try this for condition 1:

=((TEXT($E3,"0000")&TEXT($F3,"0000"))-(TEXT($E3,"0000")&TEXT(HOUR(NOW()),"00")&TEXT(MINUTE(NOW()),"00")))>0

This for condition 2:

=TEXT($F3,"0000")<TEXT(HOUR(NOW()),"00")&TEXT(MINUTE(NOW()),"00")