conditional formatting a cell when it is within a certain time bracket

[Mr Marvin]

i am looking to automate the colour formatting of cells when i enter a time into a cell against the actual current time.
for example

the actual time is 11:00:00am

when i enter a time of 10:45:00 in cell D2 i want that to highlight green (so the time would be within 30 mins of the actual time), if i enter a time of 10:15:00am it would highlight amber (as it is between 30 mins and 45 mins) and if the time i enter was 10:00:00am (it would highlight red as it be over 45 minutes)

I have an VBA button which when keyed shows the actual time is in cell L9.

can this be done.

TIA

 

[Asbestos_Jen]

Yes, but each colour needs its own CF rule. I put the rules I used in G5:G7 since my excel won't show CF rules with XL2BB. The formula in E2 is

=IF(ABS($D$2-$L$9)<=30/1440,"green",IF(AND(ABS($D$2-$L$9)>30/1440,ABS($D$2-$L$9)<=45/1440),"amber","red"))

 

[Eric W]

Another option:

david763.xlsx
	D								L
1
2	9:30
3
4
5
6
7
8
9									4/5/2024 9:51
Sheet3

Cell Formulas
Range	Formula
L9	L9	=NOW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	Expression	=ABS(MOD($L$9,1)-D2)>TIME(0,45,0)	text	NO
D2	Expression	=ABS(MOD($L$9,1)-D2)<TIME(0,30,0)	text	NO
D2	Expression	=ABS(MOD($L$9,1)-D2)<TIME(0,45,0)	text	NO

I'm not sure what else your macro does, but I just used the NOW() function for the date/time. You can refresh it with F9.

 

[Asbestos_Jen]

@Eric W can I ask why the Mod() part of the formula? Is it just to ensure the value in L9 is a valid time?

 

[Eric W]

@Eric W can I ask why the Mod() part of the formula? Is it just to ensure the value in L9 is a valid time?

Yes, the NOW() function returns the combined day and time, which would throw off the CF formula. The date is the whole number part, and the time is the decimal part. The MOD(x,1) function removes the whole number part, just leaving the time. Now that you mention it though, I think both our formulas would have trouble with 11:50 PM and 00:03 AM being less than 30 minutes apart.

 

[Mr Marvin]

thank you both for your help. after some playing i managed to work them out with your formulas.