I have a Google Sheet where a column is filled with times in a 24 hour format (XX:xx), how can I select a cell to highlight red after 3 hours have passed with conditional formatting?

I've been trying to mess with the Now() command in custom formulas for like 2 hours now, but I can't seem to get it to work. PLZ HELP!

CodePudding user response：

To Highlight cells if exactly 3 hours 03:00:00.000 have been passed you need to paste this formula in

To check and highlight cells when certain duration have been passed and be able to adjust the duration from a cell you need to paste this formula in cell B2 to get True or False output.

=ArrayFormula(IF(A2:A="",,IF(A2:A-NOW()>$E$2,"True","False")))

It can be used as a formula in

CodePudding user response：

For anyone who has the same issue, it has been solved. Looks like the Now() call requires both the time and date to be inside the cell in order to work.

The formula I used to highlight cells that are older than 3 hours is =(Now()-TIME(3,0,0))

A friend also showed me a different formula that seems to do the same thing =IF(NOW()>A2 3/24,TRUE(),FALSE())

You can change the time by changing the (3,0,0) to Hrs,Min,Sec of your choosing, for the second formula, you can change it by adjusting the fraction value (3/24 for 3 hours, etc)

These will only work if you have the date and time in the cells you are formatting (e.g. If you timestamp with Ctrl Alt Shift ;) it will not work if you timestamp with just the time (Ctrl Alt Shift) as that seems to set the year to 1899 for some reason.

It would be nice if I could get these working without a date, but I have not found a way to do that.