SOLVED

Conditional formatting based on time (00:00:00)

Copper Contributor

Please can you help me find a solution to the following question?
I keep a record of what time the user updates data. The allowed time is one hour before and one hour after the allotted time. Let's say we assign 9:00:00 as the indicator, so:
08:00:00 to 09:59:59, the cell should be green,
Every other hour, orange cell,
No update, red cell

 

Szymon1983_0-1675948772748.png

TIA

6 Replies

@Szymon1983 

Let's say the times will be entered in B2:B100.

Select this range. B2 should be the active cell within the selection.

Set the Fill Color to red. This will be the default.

 

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

 

=B2<>""

 

Click Format...
Activate the Fill tab.
Select orange.
Click OK, then click OK again.

 

Repeat these steps, but with the formula

 

=AND(B2>=TIME(8,0,0),B2<=TIME(10,0,0))

 

and green as fill color.

@HansVogelaar 

Thanks for your help, but it doesn't work for some reason. I'm doing something wrong, but I'm not sure what. Please, here is step by step of my work, maybe you can see something?

Szymon1983_0-1675953866374.png

Szymon1983_2-1675953959668.pngSzymon1983_3-1675954078513.pngSzymon1983_4-1675954117283.pngSzymon1983_5-1675954237436.pngSzymon1983_6-1675954340624.png

 

 

best response confirmed by Szymon1983 (Copper Contributor)
Solution

@Szymon1983 

Perhaps column D contains date + time values instead of just time values. Does this formula for the "green" rule work?

 

=OR(HOUR(D43)=8,HOUR(D43)=9)

Thank You Hans

You're a Star :) Is working now

Thanks
Thank you, Hans

@Barry840 

You're welcome!

1 best response

Accepted Solutions
best response confirmed by Szymon1983 (Copper Contributor)
Solution

@Szymon1983 

Perhaps column D contains date + time values instead of just time values. Does this formula for the "green" rule work?

 

=OR(HOUR(D43)=8,HOUR(D43)=9)

View solution in original post