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())
  • 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. Use four spaces to begin the segment that displays your sample data. – hedgepig Jul 8 '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 8 '15 at 18:36
  • @inkyvoyd I already fixed it ;) – DavidPostill Jul 8 '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 8 '15 at 18:42
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.