Conditional Formatting Add 8 hours

Hello world,
I am attempting to use conditional formatting to shade cells depending on completion times. Here is my setup and as always thank you very much for any and all help.

F2 = Start Time
R2 = Completion Time

Here's my formula for red.
=$R2<($F2+8/24)

Rules
Less than 8 hours from start to complete green
exact or 1 hour over yellow
greater than 1 hour over red

I believe the issue I am running into is, since all of the times are overnight, when the time rolls past midnight the formula automatically assumes that it is way over or under the set formulas. Is there a way to do this without adding new columns? I've attached my testing spreadsheet to here as well.

See if this resolves the issue:

=$R2<MOD($F2+480/24/60,1)

OK, well try this, then:

=$R2>MOD($F2+480/24/60,1)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

or try below
=MOD(R2-F2,1)<1/3

Order the rules in the order you wish Excel to apply them - move the red rule above the amber rule. Use the up/down arrows in the CF dialog to do this.

Not if you are happy for Excel to stop if they are true! In other words, not continue looking at later rules if one is met.

You need to think about the logical order you would apply the criteria and if any of your criteria are 'blurry', i.e. might overlap with each other. Does this make sense? Thorough testing should flush out any remaining issues.

No problem. As I think I said to you before (or someone else recently), CF is a bit of a black art ...

You've left the thread marked solved - remove the tag.

I'll have a look for you.

I don't think you have quite understood my comments about the order you should add the rules and whether or not to use Stop if True. Try this:

You are not paying attention!!!

Look at the ORDER of the rules in my screenshot. Yours is not the same ...

The problem is the start time - when it is more than 2 hours, it causes a problem. You are going to need to adjust your formula to check this as part of the calculation (in all of the rules).