Copy row automation returning outdated value for =today()

Application: I have a set of KPI values we need to trend in a graph over time.

Strategy: I've built a KPI tracker sheet that captures live metadata from various sources. Through a recurring weekly copy row automation, this takes a snapshot of those values and adds a row to a KPI history sheet with dates to support a report / dashboard graph.

  • The date value cell in the metadata sheet is =today(), and the history sheet is expecting an entry every 7 days.
  • The automation is set to recur 1x weekly on Thursday at 4:00 AM.

Problem: The copy row automation is returning Wednesday's date despite the automation running on Thursday. When I check the metadata sheet, it's showing the correct (Thursday) date.

I have tried changing the timing of the copy row event, but still returning the old value. I found that the cell history always shows that the cell value in the metadata sheet updates not at 12:01 AM, but 1 minute after the automation runs. This suggests to me that the sheet data is static until 'called' for either via an automation or user checking the sheet which prompts any calculations / formulas to refresh.

Curious how to either fix (preferred) or trick the sheet into refreshing the data to be current before the copy row event happens.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!