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 ...
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.
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.
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
See the attached demo workbook.
