Highlight row after 24 hour period

Options

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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/05/20
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!