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
  • 153,128
  • 77
  • 353
  • 394
Steve G
  • 11
  • 1
  • 1
    Just some friendly advice, you may want to edit the title of your question to reflect the specific question you have, which I assume is regarding excel. Something like "MS Excel conditional formatting based on time" would be more informative to question answerers and future readers that have similar questions. As for your actual question, it would be very helpful to put in a bit of a chart such as [here](http://superuser.com/questions/889201/turn-one-row-into-multiple-rows-in-excel). Use four spaces to begin the segment that displays your sample data. – hedgepig Jul 08 '15 at 18:35
  • 1
    The cell will only update - and therefore reformat - if you do something. I assume you have tried running a macro that recalculates at intervals. – Judith Jul 08 '15 at 18:36
  • @inkyvoyd I already fixed it ;) – DavidPostill Jul 08 '15 at 18:37
  • 1
    DavidPostill thanks. @Steve G, do you necessarily wish to refer to a current time, or are you more concerned with times listed on a spreadsheet? – hedgepig Jul 08 '15 at 18:42
  • https://www.ablebits.com/office-addins-blog/2014/06/17/excel-conditional-formatting-dates/#based-current-date – Kirill2485 Jul 08 '15 at 19:02
  • Updated my answer - had it backwards in my rush. – Raystafarian Jul 09 '15 at 11:56

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,583
  • 11
  • 60
  • 89