Question Using TODAY Formula
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
Best Answer
-
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!
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!
Answers
-
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:
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
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!
-
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!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!