Conditional Format with time

MFish

Board Regular
Joined
May 9, 2019
Messages
76
Hi there, I have this VBA Code in my sheet and a module -

Code:
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
JacobJohnJimmyJoanJasmineJennyJoeJane
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,867
Messages
5,638,757
Members
417,051
Latest member
arthur_w

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top