# conditional formatting based upon difference between two times

#### DDT~123

##### Board Regular
Hello All,

I have a scheduling worksheet which has an agent's start/break1/lunch/break2/end times. I also have a real-time calculator which refreshes each second. I'd like for the cell to turn yellow 3 minutes before the agent's break time, turn red when it's time for their break, continue staying red for 15 minutes, then turn yellow again when for minutes 16 thru 18 after their break start time, and finally back to white 19 minutes after their break start time. My cells are as follows:

A1 - real time clock
E6 - Break1 start time
F6 - Lunch start time

Example:
Value in E6 = 10:00:00
If the value in A1 is between 09:57:00 and 09:59:00, then E6 will turn yellow
If the value in A1 is between 10:00:00 and 10:15:00, then E6 will turn red
If the value in A1 is between 10:16:00 to 10:18:00, then E6 will turn yellow
If the value in E6 is greater than 10:18:00, but less than the scheduled lunch time in cell F6 the cell will turn white

Any help will be greatly appreciated.
If the value is greater than

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
how is the sheet updated with the clock - ?
it may need VBA to do the update as the calculation may not take place on the sheet - unless you have something already running so the sheet updates

what version of excel do you have
you could use a conditional format
TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6)-1,MINUTE(E6)+57,0)

then
TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6),0)

then
TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6)+16,0)

then
(AND(TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6)+18,0),TIME(HOUR(E6),MINUTE(E6),0)<=TIME(HOUR(F6),MINUTE(F6),0) )
But did you mean E6 ? changes

if you put these into a conditional format in reverse , then stop if true
should work OK -

Dont have a realtime clock to test a spreadsheet - or know how yours is updating

Thank you for the help with conditional formatting, etaf.

My code for the clock is:

Dim stopit As Boolean

Sub Autpen()
stopit = False
clock
End Sub

Sub clock()
If stopit = True Then Exit Sub
ActiveWorkbook.Worksheets(1).Cells(1, 1).Value = _
Format(Now, "hh:mm:ss")
Application.OnTime (Now + TimeSerial(0, 0, 1)), "clock"
End Sub
Sub StopClock()
stopit = True
clock
End Sub

The clock automatically starts when the workbook is opened and I have the NOW() formula in cell A1. I added a button to the worksheet which stops the clock whenever the agents are not being monitored.

I entered the formulas for conditional formatting which didn't change whenever the time in the real-time clock (A1) was the same as the time entered into cell E6. I have Excel '10

I think the formulas i entered will need additional elements to make sure they are between the values - which I can modify , but first

can you just try ONE conditional format and see if that changes colour on the clock change

say
TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6),0)

so once the time in A1 is greater than E6 - it should change

thanks for the code - I tested and it did work - so now I need to change the formulas slightly

i have modified the conditional format for the first condition and it appears to work
will turn yellow 3 mins before
AND(TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6)-1,MINUTE(E6)+57,0), TIME(HOUR(A1),MINUTE(A1),0)<=TIME(HOUR(E6)-1,MINUTE(E6)+59,0))

working on the others

heres the first three conditions - tested and work ALL OK

=AND(TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6)-1,MINUTE(E6)+57,0), TIME(HOUR(A1),MINUTE(A1),0)<=TIME(HOUR(E6)-1,MINUTE(E6)+59,0))

=AND(TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6),0), TIME(HOUR(A1),MINUTE(A1),0)<=TIME(HOUR(E6),MINUTE(E6)+15,0))

=AND(TIME(HOUR(A1),MINUTE(A1),0)>=TIME(HOUR(E6),MINUTE(E6)+16,0), TIME(HOUR(A1),MINUTE(A1),0)<=TIME(HOUR(E6),MINUTE(E6)+18,0))

If the value in E6 is greater than 10:18:00, but less than the scheduled lunch time in cell F6 the cell will be white

do you mean NO FILL which is the same as white

What would you want to happen after Lunch time - you dont need a format for white -
Can you explain a little more

I'm UK time so, may log off soon

Last edited:

Replies
4
Views
518
Replies
13
Views
180
Replies
17
Views
481
Replies
1
Views
91
Replies
5
Views
149

1,207,281
Messages
6,077,511
Members
446,287
Latest member
tjverdugo85

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