CODEWITHSUNDEEP
google-sheetsconditional-formatting
JBlasi
JBlasi

Reputation: 1

Google Sheets - Conditional Formatting - Shading as hours go by

I have schedule about 20 hours a day for 5 days. Each row is an hour. I want to gray out each row as the hours go by and highlight the current hour green. I have the date/time (mm/dd/yyyy hh:mm:ss) in column F displaying as HH:MM:SS.

I seem to have figured out the graying out part. I've used the NOW function in a hidden cell and set my conditional formatting to =$F5<$AK$1 F5 being my first time and AK1 being the hidden NOW() function). When I tried using =$F5<NOW() it was graying out things on parts of the schedule that represented other days. But my new formula seems to work. For the highlighting of the current hour I started off using custom formula =HOUR($F5)=HOUR(NOW()). This highlights the current hour but also highlights the current hour on other days. I've tried AND statements to check the Date and Time of the cell vs NOW() but nothing seems to only highlight the current hour of the current day. Any help would be appreciated. Thanks

Upvotes: 0

Views: 140

Answers (1)

Mart&#237;n
Mart&#237;n

Reputation: 10277

I don't know the exact format of your cells, but given your formula works, I'd suggest to add a condition to compare the days:

=AND(HOUR($F5)=HOUR(NOW()),DATEVALUE($F5)=DATEVALUE(TODAY()))

As you can see, this tomorrow's value isn't highlighted, but today's (06 feb) is:

enter image description here

UPDATE

Given your spreadsheet and format, you can use the date with TEXT:

=AND(HOUR($A1)=HOUR(NOW()),DATEVALUE(TEXT($A1,"MM/DD/YYYY"))=DATEVALUE(TODAY()))

You could also use YEAR($A1)=YEAR(TODAY()),MONTH($A1)=MONTH(TODAY()) etc...

enter image description here

Upvotes: 0

Related Questions