Time Warning

[Semper Paratus]

Hi all,

A quick question for you

If the time gap between cells A1 and A2 is less than 12 hours I want to have some kind of warning, the cell go red or a pop up message

Thanks

 

[Andrew Poulsom]

Try the Conditional Formatting formula:

=MOD(A2-A1,1)<0.5

 

[Semper Paratus]

Try the Conditional Formatting formula:

=MOD(A2-A1,1)<0.5

Perfect thank you very much

 

[Semper Paratus]

Okay I did come up with one issue

If the target cell A1 is blank (reason: Employees day off) I still need to format the cell in case they work on their day off.

 

[Andrew Poulsom]

Do you mean?

=OR(A1="",MOD(A2-A1,1)<0.5)

 

[Semper Paratus]

That formula (as the other) seems to read the blank cell as midnight and so any start time before midday the next day shows a warning.

 

[Andrew Poulsom]

Which cell are you formatting?

 

[Semper Paratus]

Cell A2

 

[Andrew Poulsom]

What would you expect for A1 blank and A2 08:00 and A2 16:00. Isn't A1 the start time?

 

[Semper Paratus]

Sorry, I'll explain it properly.

A1 is start time and A2 is finish time for example Monday

A3 is start time and A4 is finish time for Tuesday etc

I need to calculate the 12 hour gap between cells A2 and A3 as employees need to have a 12 hour rest break between shifts.

But A2 maybe blank as Monday may be their day off, but they can also work the Monday for overtime