Google-sheets – Conditional formatting for cells that have date/time within the last 24 hours

conditional formattinggoogle sheets

I have a Google sheet that has a column for "last updated" and each row represents a separate file. People manually paste in the time and date in that column, like in the screenshot.

If the date and time is not within the last 24 hours, then I want that cell to turn red. The "date is before" option only checks the date and not the time.

For this example, let's say the current time is 930 AM, so the cells that have a time before 9:30 AM yesterday should turn red. The cells that have a time after 9:30 AM yesterday should remain unaffected.

This is what I want it to look like.

This is what I want it to look like

How can I do this with Google Sheets conditional formatting?

Here is my test sheet.

enter image description here
(Click image to enlarge)

https://docs.google.com/spreadsheets/d/1DRQYrxpE61e7VMEpbuiCO543cpZvcckVr0PKPnQcPw4/edit?usp=sharing

Best Answer

...say the current time is 930am, so the cells that have a time BEFORE 9:30am yesterday should turn red. The cells that have a time AFTER 9:30am yesterday should remain unaffected.

There is a setting to choose so, right from within the conditional format menu.

For your chosen range you pick the
setting Is between and as values =NOW()-2 and =NOW()-1

Conditional formatting for cells that have date/time within the last 24 hours

Related Topic