CODEWITHSUNDEEP
excel
Randy Seymour
Randy Seymour

Reputation: 23

Time conditional formatting

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

Upvotes: 0

Views: 586

Answers (2)

Jackson
Jackson

Reputation: 225

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

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 1

Related Questions