Question Using TODAY Formula (2024)

Options

Michael Wilkesen ✭✭✭✭

01/06/22 in Formulas and Functions

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

0 · Share on FacebookShare on Twitter

Jeff Reisman ✭✭✭✭✭✭
See Also
Using today() in formulas Functions List | Smartsheet Learning Center
01/06/22 edited 01/06/22 Answer ✓

Options
@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!

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!
1 · Share on FacebookShare on Twitter

Answers

Andrée Starå ✭✭✭✭✭✭
01/06/22

Options
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!

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.
1 · Share on FacebookShare on Twitter
Jeff Reisman ✭✭✭✭✭✭
01/06/22 edited 01/06/22 Answer ✓

Options
@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!

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!
1 · Share on FacebookShare on Twitter
Michael Wilkesen ✭✭✭✭
01/07/22

Options
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!
0 · Share on FacebookShare on Twitter
Michael Wilkesen ✭✭✭✭
01/07/22

Options
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

0 · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });