# Time Warning

#### Semper Paratus

##### New Member
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

Last edited:

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Andrew Poulsom

##### MrExcel MVP
Try the Conditional Formatting formula:

=MOD(A2-A1,1)<0.5

#### Semper Paratus

##### New Member
Try the Conditional Formatting formula:

=MOD(A2-A1,1)<0.5

Perfect thank you very much

#### Semper Paratus

##### New Member
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

##### MrExcel MVP
Do you mean?

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

#### Semper Paratus

##### New Member
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

##### MrExcel MVP
Which cell are you formatting?

Cell A2

#### Andrew Poulsom

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

#### Semper Paratus

##### New Member
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

Replies
6
Views
300
Replies
5
Views
478
Replies
7
Views
664
Replies
6
Views
468
Replies
2
Views
511

1,190,919
Messages
5,983,588
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back