1

I am trying to conditionally format a cell based on time.

  • Two hours prior to a time I want the cell to turn green.

  • 1 hour before the time I want it to change to orange.

  • 30 minutes before it should turn red.

How would I do this?

I have tried the following:

=B1<(NOW()-today())
DavidPostill
  • 162,382
Steve G
  • 11

1 Answers1

1

Your conditional format rules will need to be like this -

=IF((F1-(NOW()-TODAY()))*1440<0,TRUE)
=IF((F1-(NOW()-TODAY()))*1440<30,TRUE)
=IF((F1-(NOW()-TODAY()))*1440<60,TRUE)
=IF((F1-(NOW()-TODAY()))*1440<120,TRUE)

But you'll need four rules, with the smaller ones stopping when true. Put them in the above order, but use whatever cell your times start in instead of F1

You should apply this to only the first cell then right click and drag down and hit "fill formatting only" - otherwise it will use the F1 in every case rather than each cell.

enter image description here

enter image description here

click for full size enter image description here

Raystafarian
  • 21,963
  • 12
  • 64
  • 91