Forum Discussion
Solved
Conditional formatting? highlight a cell based on current time
Working on an hourly To-Do list.
I have a column (H) with the listed hour (on the Hr) starting with 5am all the way until 11pm.
For Example:
6:00 AM
7:00 AM
8:00 AM
9:00 AM
The ask: I would like the 7:00 AM cell be highlighted when the current time is between 7-7:59 AM. Then the next cell once it hits between 8 and 8:59 and so on.
Thank you and let me know what you think?
Let's say the times are in H2:H20.
Select this range. H2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to detemine which cells to format'.
Enter the formula
=HOUR(H2)=HOUR(NOW())
(Remember, H2 is the active cell in the selection).
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Hi,
Yes please DIgiclock in A1
Thank you
Hi all,
I have created a digi clock on an excel sheet and am trying to work out the conditioning formatting to change the cell colour if the time passes. e.g if the departure time is 12:00 then the cell D15 changes red at 12:01 (my digi clock is set up HH:MM:SS)
Which cell contains the departure time? D15?
Yes please
The digiclock is in A1
Thanks for looking at this for me
Hi all,
I have created a digi clock on an excel sheet and am trying to work out the conditioning formatting to change the cell colour if the time passes. e.g if the departure time is 12:00 then the cell D15 changes red at 12:01 (my digi clock is set up HH:MM:SS)
- Just want to enhance the answer, if you have list of half-an hour column like:
6:00 AM
6:30 AM
7:00 AM
7:30 AM
you can use this formula: =AND(HOUR(H2)=HOUR(NOW()),MINUTE(H2)=(IF(MINUTE(NOW())<30,0,30))).
It's the same formula provided by Hans with addition checking minute of the listed hour column, whether 0 or 30.Najmi6240, thank you this was very helpful, just want to share with others that if you are working with 15-minute increments you can simply change the 30 in this formula to 15.
Peaceful82 Hello, I've tried this on mine and it only highlights at o'clock and quarter past, is there a way to also get it to highlight at half past and quarter to?
thanks
- Perfect. Thank you!!
Let's say the times are in H2:H20.
Select this range. H2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to detemine which cells to format'.
Enter the formula
=HOUR(H2)=HOUR(NOW())
(Remember, H2 is the active cell in the selection).
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
- What if I wanted to highlight the whole row instead of just the cell with the time in it? Highlight all of row 2 in this case?
Select rows 2 to 20 (or however far down you want).
Follow the steps from my previous reply, but use the formula
=HOUR($H2)=HOUR(NOW())
The $ before the column letter H ensures that the rule "looks at" column H in all cells of a row.
