Use of "TODAY" function

Chuck May

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?

Mark Cronk

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

cschoenfeld

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

@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")

cschoenfeld

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