Highlight row after 24 hour period
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
Answers
-
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.
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!