2

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

1
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept and even upvote it so others can benefit as well. Commented Feb 14, 2020 at 17:18

2 Answers 2

1

...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

7
  • thanks for trying! tho this option only looks at the date and not the time. So if the current time was 12am on 02/14 it would still mark 11pm on 2/13 as red even tho it was only an hour ago. I need it to check it it has not been withing 24 hours. Commented Feb 14, 2020 at 17:21
  • 1
    I don't think so. It looks at both the date and time. Have you tried it? In case you still have issues, please read how to and share a test sheet so as you can be easier helped. Commented Feb 14, 2020 at 17:24
  • docs.google.com/spreadsheets/d/… Commented Feb 14, 2020 at 17:29
  • Thank you @marikamitsos! Commented Feb 14, 2020 at 17:38
  • You were right. Please check my updated answer. Commented Feb 15, 2020 at 10:49
2

It looks a little complicated in Google Sheets, but the formatting for a query that you could use in the conditional formatting is here: https://infoinspired.com/google-docs/spreadsheet/datetime-in-query-in-google-sheets/

2
  • Ive been trying since your answer but i still cant get it right :( Should i use "exact formal" in Conditional Formatting? Or should i use "Date is before" and "exact date"? Commented Feb 14, 2020 at 17:15
  • Here is my test sheet: docs.google.com/spreadsheets/d/… Commented Feb 14, 2020 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.