TODAY() not updating till the file is opened

Avinash Chandra

We have using TODAY() functionality since 2018-2019 with no issues, why is this issue coming up now? We have opened ticket and I have been told to implement a workaround that according to the feedback in this community is not a full proof workaround. Here is the ticket number:

08096290: Schedules only refresh when the files are opened [ thread::A5FKDp2IfSySg8Ed2LHSbCY:: ]

Smartsheet should look what was changed this year. Kindly advise.

Paul Newcome

@Avinash Chandra This has been the case for as long as I can remember. The sheet must be activated for the TODAY function to update. Whether this is through actually opening the sheet, update requests, form submissions, automations, or other methods, the sheet needs to be activated.

The method I use is to insert a date type column and set a Record a Date automation to run everyday at 12:00am and record the date in my date type helper column. I have never had an issue with this since I implemented it.

Avinash Chandra

Thanks Paul, My only concern is that this issue was not an issue since 2019, why now? Kindly advise.

Paul Newcome

It was an issue back then as well.

Avinash Chandra

Good to know, we have over 1000 schedules that use this functionality and only 50 of them are encountering this issue. all others are refreshing as usual. Kindly advise.

Thank you for your prompt response as always!

Paul Newcome

It sounds like the rest that are working are being activated in some way. Activating a sheet can also include cell links and cross sheet references, so it is possible the ones that are working are being activated in some way that the others are not.

Avinash Chandra

Absolutely, You are correct! thanks Paul for all your help and advise.

Paul Newcome

Happy to help. 👍️

Jake Gustafson

Agreed that this is annoying. My workaround has been to create a Lock Rows automation to occur at 1:00 AM and an Unlock Rows automation to occur at 2:00 AM. Depending on what time zones you have people working in, that might not work as well or you may need to dial it in to some very specific row records to avoid inconveniencing anyone. For me, it wasn't as noticeable like Paul N mentioned with sheets with a high amount of activity, where new Form submissions were refreshing the data. But when just looking at Dashboards and metrics, it becomes noticeable quite quickly.

Paul Newcome

@Jake Gustafson I personally use the Record a Date method so I only have to set up one automation, although that does count towards cell limits in larger sheets.