Time conditional formatting

0
votes

I'm trying to create a spread sheet where a cell will turn Green then Yellow Than Red based on time pasted from time recorded. I was using: 

=HOUR(MOD(NOW(),1)-C2)<2  as Green

=HOUR(MOD(NOW(),1)-D1)<4  as Yellow

=HOUR(MOD(NOW(),1)-D1)>=4  as Red

But every thing is going red I am using conditional formatting and cells are formatted as HH:MM AM/PM

2
excel
Why do you have MOD()? Hour returns a number, don't know what C2 or D1 so it's hard to say what is wrong - Andreas
D1 is the cell with the time I changed the other 2 in the sheet for got to change it here but all are looking at D1 as the "start time" and needs to change color depending on the amount of time passed like Red as " 4 hrs have passed" - Randy Seymour
Does D1 contain the time? Or does it contain the date/time formatted to show just the time portion? And, if it contains a date/time, what colour do you want to use if it is 27 hours old? (Or does D1 contain a text string that looks like a time?) - YowE3K

2 Answers

1
votes

The only way I can replicate your problem is if D1 contains a date/time, and not just a time.

Say you have a date/time in that cell of 17 Nov 2016 6:50 AM (displayed in HH:MM AM/PM format as "06:50 AM") and say it is now 17 Nov 2016 7:59 AM.

Your calculation will be attempting to calculate the HOUR of 0.28472222 (the numeric value for 6:50 AM on 0 Jan 1900) less 42691.33264 (the numeric value for 7:59 AM on 17 Nov 2016). An HOUR(-42691.04792) issues a #NUM! error (as negative values are invalid for date/time calculations) and will give you problems in your conditional formatting.

If you do have a date/time in cell D1 (even though it is formatted to just display the time portion), then you can either use the following formulae if you want an elapsed time of 27 hours to display as red:

=(NOW()-D1)*24<2    as Green
=(NOW()-D1)*24<4    as Yellow
=(NOW()-D1)*24>=4   as Red

or use the following formulae if you want an elapsed time of 27 hours to be treated the same as 3 hours and formatted as yellow:

=HOUR(NOW()-D1)<2    as Green
=HOUR(NOW()-D1)<4    as Yellow
=HOUR(NOW()-D1)>=4   as Red
0
votes

Break it down into pieces to make sure each portion of your function is acting as expected. I'm not entirely sure what your goal is, but I've broken down each piece of your example. Using this info you should be able to see where the output becomes incorrect and fix the logic. Let me know if it helps but you aren't quite there.

What you have:

fx: =NOW()

Output: 11/16/2016 15:16

fx: =MOD(NOW(),1)

Output: 1/0/1900 15:16

fx: =HOUR(MOD(NOW(),1))

Output: 15

fx: =HOUR(MOD(NOW(),1)-D1)

Output: 15 - HOUR(D1) (as a number)

Is 15 - HOUR(D1) the number you expect to be using in your comparison? If not, you've got an error in your logic. If it is, you probably have an error in your conditional formatting.

HTH, let us know what you come up with.

Jackson

Edits:

  • Corrected =MOD(NOW(),1) in second formula line