Using condtional formatting for a time range

[lesoies]

I have four cells, two are for the time range (start & end) when someone is available to work, the other two are scheduled times.

What I want to happen is the text color changes to red when the scheduled time doesn't fit into the availability.

This is what I have so far, but it doesn't work properly:

Please Login or Register  to view this content.

Help appreciated!

[FDibbins]

It would be much easier to offer suggestions if we could see a sample of what you are working with?

[lesoies]

Here you go

[FDibbins]

OK try this....
for the start time...
=AND(E5<>"",E5<E8)
fir the end time...
=AND(F5<>"",F5<F8)

[lesoies]

But what if the shift start time (E6) is 1am, and the availability is 11pm

[FDibbins]

Then it will flag it as TRUE - it will highlight that this is a problem

[lesoies]

Any suggestions?

[FDibbins]

to what? If "shift start time (E6) is 1am, and the availability is 11pm" then it should flag the cell - and that is exactly what thte CF formula will do for you.

What answer were you expecting?

[lesoies]

If the availability is 11pm-7am, and the actual shifts start time is 1 am, it returns true. I just need to figure out how to add an if statement into this properly to return false, on the condition that the availability end time is less than the availability start time.