Why isn't our column formula updating on its own?

EZayas

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?

Paul Newcome

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.

Jeff Reisman

@EZayas

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.

Paul Newcome

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.

Emilio Wright

@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.