Highlight row after 24 hour period

Anthony Roberts

hello,

working on a sheet where product must be "quarantined" for a 24 hour period.

is it possible for SS to automatically count down after anew row is added then highlight said row after the 24 hour period. it must be 24 hours to the exact second

L_123

Interesting question. In order for this to work you have to play the system a little bit.

You would need:

Created Column

Modified (date) column

Checkbox Column

Output column

This can be used to create a reporting system to see if the 24 hour mark has been passed or not.

The secret to this is if you subtract a modified or created cell from another date/time cell, it will give you a return in the following format

1.00001

which represents 1 day and 1 second. So we first need to figure out how many seconds are in a day

24H/1Day*60M/1H*60S/1M

or 24 * 60 * 60

which is 86400

or in the format above .864

Knowing that you can create what I have pictured below

(The formula below the red circle is what is actually written in the red circle)

With this you can click the holder block and save the sheet to modify the "Modified" column to the current date/time. Then the formula will respond with a green circle if you can report yet.

L_123

To apply this on a row by row basis you simply have to flip the formula around a bit, then drag it down.

=IF(MAX(Modified:Modified) - Created@row < 0.864, "Red", "Green")