Conditional formating depending on employee availability

[zanael]

Hello there !!

I created a small scheduling tool for my team

I just added an employee availability tab, and i m trying to have scheduled shifts cells turn red if those shift are outside of employees availability

For punching in it s pretty easy : if starting time is earlier then starting availability...cells turn red

Was considering doing the same for punching out time ( greater instead of less than) but there s a hicup

My problem is for punching out time if, let s say, an employee is available until 1 am but is scheduled until 11pm, because excel "thinks" that 1 am is earlier than 11pm, therefore cell turns red.

There s an example under MER tab (employees availability is on DISPOS tab)

Can anyone please give me a hand on that one please ? (text is in french but all formulas are entered in english)


Thanks !

[FlameRetired]

Haven't seen your file, but have you tried including the Date in each cell with the times? It sounds like that could resolve.

Even referencing both a separate Date column along with the times in your formulas might do it.

[zanael]

i ll give it a try , thanks !