Question Using TODAY Formula

Michael Wilkesen · 01/06/22

Good day, I have a basic question, is there a way to use the TODAY function in a formula that will run the calculation and update my sheet without having to open the sheet everyday to "trigger" the calculation?

What I am getting at is I have a sheet that uses TODAY in many calculations, however, due to the size of the sheet, my team almost always uses reports & Dynamic Views to manage their projects and we almost never open the primary data sheet. This obviously causes issues with date calculations when using the TODAY function in the a formula.

Any suggestions???????

Thank you in advance.

Michael

Jeff Reisman · 01/06/22

@Michael Wilkesen

A good workaround I found is to create a hidden helper Date column called "CurrentDate", then create a "Record a Date" automation rule that everyday places the current date in that column for every populated row in the sheet (use the condition block with something like Where Primary Column is not blank.) Then your formulas can use CurrentDate@row instead of TODAY() and won't need the sheet opened in order to refresh the current date.

Edit: Just realized Andree pointed you to the same workaround :) Good luck!

Andrée Starå · 01/06/22

Hi @Michael Wilkesen

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!

Have a fantastic week & Happy New Year!

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!

Jeff Reisman · 01/06/22

@Michael Wilkesen

A good workaround I found is to create a hidden helper Date column called "CurrentDate", then create a "Record a Date" automation rule that everyday places the current date in that column for every populated row in the sheet (use the condition block with something like Where Primary Column is not blank.) Then your formulas can use CurrentDate@row instead of TODAY() and won't need the sheet opened in order to refresh the current date.

Edit: Just realized Andree pointed you to the same workaround :) Good luck!

Michael Wilkesen · 01/07/22

Thank you Jeff! I appreciate the workaround....this is why I love the Smartsheet community, I get to amp up my game and look like a Rockstar!

Michael Wilkesen · 01/07/22

https://community.smartsheet.com/discussion/comment/315160#Comment_315160

Thanks Andree! I think this will work like a charm. You peaked my interest with "This could also be structured so you'd use a so-called helper sheet for the process above and then link it to all other sheets where you'd need the daily update."

I would like to hear your thoughts on how that might look. I manage multiple projects and if I could simply update every project sheet to point to the same "helper sheet" (which I totally utilize for metrics and dashboards-total game changer) that would be amazing.

Michael

Question Using TODAY Formula

Best Answer

Answers

Help Article Resources

Categories