=TODAY() function will not remain dynamic

Paul Newcome

@SteveReed@WK I'll have to dig through some of the old threads and see if I can find where this idea was originally brought up.

Jenna Bailey

Can we revisit this? @Paul Newcome ... I applied the lock/unlock and it just doesn't seem to want to work. I've tried cell linking and a single sheet with just a today function, and nothing seems to want to work. You mentioned the cell linking method - does that mean that you go in to your TODAY sheet every day to ensure the update?

Jenna Bailey

Actually, I figured out a work around that works well for me - I am just using a datamesh that runs daily.

Paul Newcome

@Jenna Bailey The lock/unlock is something that I personally have not tested. It is something that other users have said works for them.

With the cell linking, I have a "TODAY" sheet that gets a new row added every day just after midnight through Zapier and then in my other sheets use a cell link to pull the date over. I then reference that linked cell in my formulas in place of using the TODAY function.

Since this has been working very very reliably for me, I didn't see the need to try to change it.

Thinh Huynh

Hi @SteveReed@WK ,

You can try to use Approval Request workflow to manually update your TODAY sheet. Hope that helps!

I tried this and it worked. The Today cell in RED is updated after I did Approve/Decline, and all my other sheets that linked to the RED cell are updated also.

BD

https://community.smartsheet.com/discussion/comment/267334#Comment_267334

Thanks @Paul Newcome . While researching your Zapier option, I realized I could use Power Automate. Power Automate is included in our MS Office 365 license.

Paul Newcome

@BD I've never used the Power Automate feature, but that is good to know. There is also another option now that can be done completely within Smartsheet using the Record A Date Automation as well.

Craig Moss

@Paul Newcome , Never mind... I did not view the second page of this thread before posting, and it gave me some things to try. I'll start with Record-a-date. Thanks

Original post:

You mentioned above that you have your Today sheet set to update daily. How do you do this if you are not using the Lock/Unlock automations? I am just seeing this post and would like to have a solid approach before I attempt it.

I have been using Zapier to do this daily update for several years in numerous sheets, but recently I have been getting errors where my Zaps are getting turned off and have to be manually turned back on. I can't get either Smartsheet or Zapier support to own and resolve the issue. Before that, I had been steadily converting Zaps to automations anyway as Clear Cell/Change value/Record a date were released, and would love to do the same with the Today update. So done with Zapier.

Thanks in advance!

Andrée Starå

Hi @Craig Moss

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Richard Heath

I've come across this thread as we now are encountering the same issue.

The solutions suggested above seem overly complex for a simply =today() function to refresh. We are using a Dashboard as a Payment Certificate that scrapes today's date of a Sheet, so our team struggles to work out why the date in the Dashboard is usually a few days behind.

Has anyone worked out a simple approach to having =today() refresh at midnight?

Andrée Starå

Hi @Richard Heath

I hope you're well and safe!

As far as I know, it's the only option (or similar) at the moment in Smartsheet.

Other options would be the Smartsheet API or a 3rd-party option.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

TJ North

@Richard Heath Try setting up automation using the 'Record a Date' function. Set it up to run 1x per day & log "today's" date in every row you need to calculate values using "today's" date. Example I use is below. I believe I found this idea off of another posting to the community.

Agreed it would be ideal if the sheet did not need to be activated in some way for the TODAY function to assist in calculations.

Richard Heath

Thanks @TJ North . That has worked well and appears to update the Sheet (and the cell) on a daily basis.

I appreciate the help.

Aly.OlsonTurek

@Paul Newcome your original answer refers to using automations to get the TODAY function to refresh. I have built a sheet that uses TODAY, and then weekly an automation runs that should copy the date & another column in the sheet to a secondary sheet. However the TODAY function is not updated at the time the automation is run unless we open each sheet. Is that expected behavior?

Paul Newcome

@Aly.OlsonTurek Yes. That is expected behavior. The Copy Row automation only copies existing data.

For an Automation to update the TODAY function, it would need to change something on the sheet.

I generally insert a date type column and set up a Record A Date automation set to run daily. This outputs today's date in the date type column which forces a refresh on the TODAY function. Really you can use anything as long as it changes at least one cell every day. Changing the date in a date type column is (in my opinion) the most efficient because it only requires one column and one automation.