How to have a cell change color based on time of day?

[biasif]

Hi all,
I'm having trouble formatting a cell to change colour depending on time of day.

The cell has no colour to start.
At 8:45 am, I would like the cell colour to change to yellow.
At 9:00 am, I would like the cell colour to change to red.

Is there way to do this with conditional formatting?

Any help is appreciated
frank

 

[Toadstool]

Hi Biasif,

This does as you ask:

Biasif.xlsx
	A
1	Rows 2 to 10
2
3
4
5
6
7
8
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=(NOW()-INT(NOW()))>=TIME(9,0,0)	text	NO
A2:A10	Expression	=AND(NOW()-INT(NOW())>=TIME(8,45,0),(NOW()-INT(NOW()))<TIME(9,0,0))	text	NO

 

[biasif]

Thanks Toadstool,
I can't get this to work.
I am selecting the cells range, opening conditional formatting, selecting new rule, then the use formula option.
In the formula window I am copying the expression you provided.

What am I doing wrong?

frank

 

[Toadstool]

What am I doing wrong?

I can't see from this end of the internet so let me give you a couple of screenshots and see if they can help.

 

[biasif]

I can't see from this end of the internet so let me give you a couple of screenshots and see if they can help.

View attachment 50145

View attachment 50146

That's what I have but it's not working. Do I need a cell with the time of day to use as a reference?
I thought that 'NOW' in the formula was the time indicator

frank

 

[Toadstool]

The first question must be: "What is the time where you are?"

The cells will not be coloured when midnight arrives and will remain that way until 08:45am when they turn yellow, then at 9:00am they change to red and remain that way until midnight arrives again.

 

[biasif]

The first question must be: "What is the time where you are?"

The cells will not be coloured when midnight arrives and will remain that way until 08:45am when they turn yellow, then at 9:00am they change to red and remain that way until midnight arrives again.

I am in the eastern time zone. It is 2pm.

 

[Toadstool]

Let's check the formula.

Enter

=(NOW()-INT(NOW()))>=TIME(9,0,0)

into any cell of your sheet. It should return TRUE

Excel holds dates as integers since 1 Jan 1900 and time as fractions of a day. This formula subtracts the integer of NOW() (the date) from the date and time so leaving a fraction
Enter

=(NOW()-INT(NOW()))

into any cell. If it's around 2pm where you are then it should return approximately 0.8119

The TIME does the comparison so enter

=TIME(9,0,0)

in a cell. It should return 9:00 AM

If those do as expected then it must be a challenge around the Conditional Format.

 

[biasif]

Let's check the formula.

Enter

=(NOW()-INT(NOW()))>=TIME(9,0,0)

into any cell of your sheet. It should return TRUE

Excel holds dates as integers since 1 Jan 1900 and time as fractions of a day. This formula subtracts the integer of NOW() (the date) from the date and time so leaving a fraction
Enter

=(NOW()-INT(NOW()))

into any cell. If it's around 2pm where you are then it should return approximately 0.8119

The TIME does the comparison so enter

=TIME(9,0,0)

in a cell. It should return 9:00 AM

If those do as expected then it must be a challenge around the Conditional Format.

The above worked as you stated.
I am trying again. I don't think excel is refreshing properly.
I am using excel at the office, over a network. I don't know if that has anything to do with it but I will try the same formatting
when I get home and will post the result.

Thank you for your help
frank

 

[Toadstool]

You're welcome.

Are you sure it's recalculating? Try F9 and see if it works.