conditional formatting based upon difference between two times

[DDT~123]

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

 

[etaf]

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

 

[DDT~123]

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.

 

[DDT~123]

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

 

[etaf]

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

 

[etaf]

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

 

[etaf]

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

 

[etaf]

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


i cant load an example spreadsheet here