Use of "TODAY" function

Options

It is my understanding that if one types in the word "TODAY" in a date field, then each time the spreadsheet with that field is opened or saved, the date in the cell in which "TODAY" has been entered will be UPDATED to the current date.

This feature is not working for me at all.

I obviously am doing something wrong.

Can anyone give me any pointers?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Chuck,

    The function is =TODAY() and will return today's date. You can place a number in the parens to get a past or future date. TODAY(7) returns the date 7 days in the future.

    For more information see below.

    https://help.smartsheet.com/function/today

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • cschoenfeld
    Options

    I have a Confirmed Start Date. 7 days after the person starts, I want that on a list to trigger something next. So, Confirmed Start Date + 7 days and have it stay on the 'to do' until it's marked completed. I've been using this but the date calc isn't working and showing 'yes' as expected. Any help is appreciated.

    =IF(AND([Confirmed Start Date]@row <> "", [Confirmed Start Date]@row >= TODAY() + 7, [Sched OB/PRR Train]@row <> "Complete"), "yes", "no")

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 03/13/23
    Options

    @cschoenfeld Let me see if I'm following your logic correctly:

    At 7 days after the confirmed start date, if the Sched OB/PRR Train is not "Complete", flag the row until Sched OB/PRR Train is "Complete".

    If that's correct, let's fix this formula:

    =IF(AND(ISDATE([Confirmed Start Date]@row), ([Confirmed Start Date]@row + 7) <= TODAY(0), [Sched OB/PRR Train]@row <> "Complete"), "yes", "no")

    We're using ([Confirmed Start Date]@row + 7) <= TODAY() because that says we're looking for rows where the current date is 7 or more days after the Confirmed Start Date. You could also do the math like this:

    (TODAY() - [Confirmed Start Date]@row) >= 7 or you could even just use the TODAY function's built in math:

    [Confirmed Start Date]@row <= TODAY(-7)

    That all being said, there's a limitation to the TODAY function. The value of TODAY() in the sheet only gets updated when the sheet is opened by someone. On days when nobody accesses the sheet, the TODAY value won't update. To get around this, I usually add a "CurrentDate" date-type column to my sheets that rely on date calculations. Then I set an automation rule that runs every morning at around 4am, which uses the Record a Date action to put the current day's date in the CurrentDate column. Then I use that column as my "Today" value:

    =IF(AND(ISDATE([Confirmed Start Date]@row), ([Confirmed Start Date]@row + 7) <= CurrentDate@row, [Sched OB/PRR Train]@row <> "Complete"), "yes", "no")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • cschoenfeld
    Options

    This worked perfectly, thank you so much for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!