Why isn't our column formula updating on its own?
In our column formula, we have a date formula based on TODAY(), however, it does not seem to update automatically when the month changes. How do I get it to update automatically at the beginning of the month?
Best Answer
-
You shouldn't even have to replace the TODAY functions with the CurrentDate@row references because the automation basically forces a sheet refresh for all cells. It doesn't hurt except on rows that are added after the update. That cell will be blank and could throw an error in your formulas until the automation runs again.
Submitting a form or an update request is also another way to get the sheet to refresh without having to actually open it.
Answers
-
The problem is that the TODAY function does not update to the current date until the sheet is opened. There is a workaround though.
Create a helper date-type column (that you can hide if you want) called something like "CurrentDate". Create an automation that Records the Date in that column and set it to run at 1am every morning. In your formulas, replace the instances of TODAY() with CurrentDate@row.
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!
-
You shouldn't even have to replace the TODAY functions with the CurrentDate@row references because the automation basically forces a sheet refresh for all cells. It doesn't hurt except on rows that are added after the update. That cell will be blank and could throw an error in your formulas until the automation runs again.
Submitting a form or an update request is also another way to get the sheet to refresh without having to actually open it.
-
@Paul Newcome Is there a real solution that works for this issue? I am using Control Center, and going back to each of the current 300 provisioned sheets to add a workflow is not something that I have in my current plan.
I suppose I could use a Global Update to "update" the column formula. Basically just using the Modify Existing Column feature and keeping the column formula the same. I have not attempted this to see if it works, but this really seems like a fault on Smartsheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!