0

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.

Improve this question
2
  • Your examples don't work out - 8 AM (08:00) is less than 11 hours after midnight (00:00), so wouldn't G8 be red? 11 Hours after midnight is 11:00, which is greater than 08:00. Commented Dec 22, 2017 at 17:59
  • @BruceWayne I got your point. But it turned correctly blue when I placed 12h and I thought that it would change to red when I placed the 8h (8:00 am), you know? But it didn’t happened. Commented Dec 22, 2017 at 22:11

1 Answer 1

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This will still create a number with an integer for the day. You will need to strip the integer day to only get the time portion.
@ScottCraner how can I do that?
@paulinhax =MOD(...,1) where ... is the formula above.
@ScottCraner - Ah! Thanks very much for pointing that out, I've edited it in to my answer.
@BruceWayne thanks for that! although when I tried to use that, I got only 0:00 as an answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.