Conditional formating 3 time frames

[Havreliuc]

Hello everyone.

Need a bit of help with some time frames. I have a fixed hour which i have to split in 3 status, early (with yellow),on time(with grees) and delay (with red).

Let's say 09:00 AM is the target hour, 30 minutes before and 30 after is "On time" with green. I need between 8:30 - 09:30 to be green,below 08:30 early with yellow and above 09:30 to be delay with red.

The formula i have right now is below, but with different time frames.

=IFERROR(IF((AD3-AC3)>0.041666667,"early",IF(AC3<=AD3,"on time","delay")),"")

Cell AD3 is 09:00AM and cell AC3 is completed manually.

Thank you!

[Havreliuc]

I did upload the worksheet, i hope this helps.

Sorry for not translating the worksheet, only columns AC, AD and AE are of intrest.

AC is arival hour
AD is maximum arival hour
AE is the status.

[FDibbins]

So where do you want the CF to be applied to...and based on which time?

[Havreliuc]

So where do you want the CF to be applied to...and based on which time?

The CF is on cell AE based on the data in cell AD and AC. I need the status "On time" to be displayed in an interval of 1hr, for example if AD is 09:00AM and AC is 08:30AM to be "on time", and also above, AC 09:30AM and AD 09:00AM also "on time". Everything below 08:30AM to be "early" and above 09:30AM "delay"

[Havreliuc]

Let me try to be more specific, and simplify things. Below is the formula and i have 3 conditional formating on that.


=IFERROR(IF((AD3-AC3)>0.041666667,"early",IF(AC3<=AD3,"on time","delay")),"")

Conditional formating rules in cell AE3 based on the value of AC3 and AD3:
If the cell contains "early" display with yellow
If the cell contains "on time" display with green
If the cell contains "delay" display with red

In cell AD3 is a fixed Hour, 09:00AM
In cell AC3 is variable time, lets say 08:30AM

What i want is, if the time in cell AC3 is between 08:30AM and 09:30AM, compere it with cell AD3 09:00AM and display "on time" in cell AE3.
If the time in cell AC3 is 08:20AM or 09:40AM display in AE3 "early" or "delay"

Ignore the IFERROR in the formula, i have it to get rid of #errors if AC3 have no value in it.

This is the best i can do

[AliGW]

Just to be very clear, conditional formatting is not what you want help with here - it's the formula that generates the word that the already existing CF will use - is this correct?

This should do it:

=IFERROR(IF(AND(AC3>=AD3-(1/48),AC3<=AD3+(1/48)),"on time",IF(AC3<AD3-(1/48),"early","delay")),"")

[protonLeah]

Please Login or Register  to view this content.

[Havreliuc]

Just to be very clear, conditional formatting is not what you want help with here - it's the formula that generates the word that the already existing CF will use - is this correct?

This should do it:

=IFERROR(IF(AND(AC3>=AD3-(1/48),AC3<=AD3+(1/48)),"on time",IF(AC3<AD3-(1/48),"early","delay")),"")

Yes AliGW, you are absolutely right. My mistake with the explanation, i know the theory but i'm not good with the explanation.

The formula works like a charm, thank you very much for your time and sorry for being a noob

Take care!

[AliGW]

No problem, but you need to take care with thread titles in future, because this one was completely misleading in the event.

[Havreliuc]

No problem, but you need to take care with thread titles in future, because this one was completely misleading in the event.

I know, i'm new on the forum and as you can see i have problems explaining myself

With time, i'll gain more knowledge through you and many others and i'll ask the right questions.