Conditional Format with time

[MFish]

Hi there, I have this VBA Code in my sheet and a module -

Option Explicit

Dim SchedRecalc As Date

Sub Recalc()

With Worksheets("Sheet1").Range("B2")

.Value = Format(Time, "hh:mm:ss AM/PM")

End With

Call SetTime

End Sub

Sub SetTime()

SchedRecalc = Now + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "Recalc"

End Sub

Sub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

End Sub

So, in Cell B2, it's a running clock when I set the hotkey to "Ctrl-k". Once "Ctrl-k" is clicked the timer works. No complaints there.

Now, what I'm trying to accomplish is to have a CF on a cell referring to the actual time. I have employees that start at random times, 5 am, 7 am, 7:30 am, etc... and I want to reference their start times to when they should be taking a lunch, through CF.
I need the employees to take their lunch before 5 hours of work-time, depending on their actual start time. I.E., if Billy started work at 5 am, then he must take his lunch by 10 am.

I want the cell (Where I type in his start time - I7) to reference the actual time in B2 to notify the user by "Green", "Yellow", and "Red". The color schemes would reference "Green" for the first 4 hours working, no problem. From hour 4-5, the filled background of that cell would reach "Yellow". Anything past 5 hours would be "Red". Now, once I put a value in I8, as a starting time for their lunch, the CF goes back to nothing stating the person has taken their lunch and shouldn't worry about them.

Is this possible?

 

[Yongle]

Yes like this

Create the rules for I7 in this sequence
Rule1 (format GREEN)
=$B$2-I7<4/24

Rule2 (format YELLOW)
=AND($B$2-I7>4/24,$B$2-I7<5/24)

Rule3 (format RED)
=$B$2-I7>5/24

Rule4 (format NO COLOUR)
=I8>0


Having created the rules for I7, copy (or drag) I7 across to J7, K7 etc for other employees (which, I assume, is what you are wanting)

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
1​
2​	TIME is now :	07:02​
3​
4​
5​
6​									Jacob	John	Jimmy	Joan	Jasmine	Jenny	Joe	Jane
7​								Start	01:30​	01:30​	02:30​	02:30​	03:30​	04:30​	05:30​	06:30​
8​								Lunch		06:30​		06:30​
9​

Sheet: Sheet1

 

[Yongle]

I should have mentioned in previous post, that the above assumes that the times are NOT crossing midnight (because your value in B2 is TIME rather than NOW)
ie everyone leaves work before midnight on the same day as they start work

In principle, all values in formulas must consider the day element + the time element
(but day element can be ignored if everything happens on the same calendar day)

For anyone reading, who may be puzzled ...

A time is a number between 0 and 1 formatted differently
A date is an integer value
(according to Excel the world began on 1 January 1900 which is what Excel returns if 1 is formatted as a date)

as a time ONLY
6am = 0.25
noon = 0.5

as a date and time
25 June 2019 at 6am = 43641.25
25 June 2019 at noon = 43641.5

26 June 2019 at 6am = 43642.25
26 June 2019 at noon = 43642.5

Someone working from noon on 25th to 6am on 26th obviously has worked 18 hours
and 43642.25 - 43641.5 = 0.75 (.75 X 24 hours = 18 hrs)
but as time only 0.25 - 0.5 = -0.25 which appears odd until we add 1 day, -0.25 + 1 = 0.75