Conditional Formatting Times

[kimberlyd.]

Cell A1 is expected finish time of 11:50 PM. Cell A2 is actual finish time.
I need to conditionally format cell A2 to show green if finished on or ahead
of time, show yellow if finished between ontime and 10 minutes late, and red
anything later than 10 minutes late. The times often go into the next day. An
example of a finish time is 12:40 AM which I need to show as red. Thanks in
advance.

 

[Stefi]

You must mark somehow the finish day. If expected finish time is 11:50 PM and
actual finish time is 12:40 AM, one can't decide if it's the same day and the
job is done ahead of time or it's the next day and the job is done late.

One way could be to mark same day with 0 in B2 and next day with 1 in B2. In
this case insert
=$A$2+$B$2>$A$1+TIME(0,10,0)
as first condition (red)
=$A$2+$B$2>$A$1
as 2nd condition (yellow)
=AND($A$2+$B$2<=$A$1,NOT(ISBLANK($A$2)))
as 3rd condition (green)

Regards,
Stefi

„kimberlyd.†ezt írta: