CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
paulinhax
paulinhax

Reputation: 602

excel: conditional formatting not working properly

I guess this is a question that has a stupid answer but I really couldn't fix this out.

I'm trying to make a hour comparison. I need to take any hour in column D and sum 11 hours (or 0,458333333333333) and if this value is less than the hour in corresponding row in column G then the value inside the cell should be blue. If the opposite is true, then the value should be red.

Let's take row 8 as an example. I'm using these formulas below:

Red

=(D8+0,458333333333333)<G8

Blue 

=(D8+0,458333333333333)>=G8

As the print shows:

enter image description here

And that had worked the first value I inserted on cell G8. As 12h is greater then 0h+11h (11h AM), it turned correctly blue. But when I changed the value to 8h, it doesn't turn into red as you can see below.

enter image description here

I was supposing that this would chande dynamically. I'm not very used with conditional formatting, so I'll appreciate any suggestion to fix this little issue.

Upvotes: 0

Views: 417

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

For your formula, you can use TIME() to make more logical statements. Try this

For BLUE:

=$D5+TIME(11,0,0)<$G5

And for the rest, just format the color of the times in column G as Red. No need to use CF unless you really want to. If so, it'd just be 

=$D5+TIME(11,0,0)>=$G5

Edit: Per @ScottCraner's excellent comment, you'll want to strip the integer day:

=MOD($D5+TIME(11,0,0)<$G5,1)

Upvotes: 1

Related Questions