Conditional formatting 3 diffrent time-frames

[Havreliuc]

Hi everyone!

I need a bit of help with the formula below. I need to solve 3 time-frames and after that to use conditional format to display 3 different colors.

So, the problem is like:

I have a cell with arrival houre, one with maximum departure hour and the final one with the status.

I used the formul below to fill the status with green or red color "=IF(W4<=X4,"on time","delay")"

If the arrival hour is smaller than the maximum departure hour, display on time with green in the status cell, if not delay with red.

The problem i have, i need one mode color, yellow for example. If my truck arrives 2 hours in advance, display "on time", if more than 2hrs display "early" with a diffrent color.

Thank you

[bebo021999]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[ORoos]

Hi Havreliuc,
You could use a 3-colour scale in conditional formatting.
New Rule
Format cells based on their values
Default will be a 2-colour scale. Change in the drop-down to 3-colour scale
For each of the 3 options;
Change the type to Formula
Value > enter the relevant formula for each of the criteria

Trust this helps.

[Havreliuc]

Hi Havreliuc,
You could use a 3-colour scale in conditional formatting.
New Rule
Format cells based on their values
Default will be a 2-colour scale. Change in the drop-down to 3-colour scale
For each of the 3 options;
Change the type to Formula
Value > enter the relevant formula for each of the criteria

Trust this helps.

Thank you for the answer!

But how do i display the 3rd status "Early" in the status cell?

[ORoos]

if we have the arrival time in A6 and the Max Dep time in B6:

=IF((B6-A6)>0.08,"early",IF(A6<=B6,"on time","delay"))

0.08 is the decimal value for 2 hours. First we check if the arrival is more than 2 hours = early, then do your text as above.

[Havreliuc]

if we have the arrival time in A6 and the Max Dep time in B6:

=IF((B6-A6)>0.08,"early",IF(A6<=B6,"on time","delay"))

0.08 is the decimal value for 2 hours. First we check if the arrival is more than 2 hours = early, then do your text as above.

Thank you! You are a life saver!

[ORoos]

You are welcome.

Please mark the post as 'SOLVED' if the solution works as required (after testing).

Feel free to click the 'Add Reputation' if happy with the solution.