=NETDAYS formula updating in the afternoon

I am using the formula below to count the number of days since an item was approved, to today. For some reason it updates in the afternoon. I believe the update happens at 3pm cst. It makes the data look unreliable because it feeds a lot of metrics. For example, it feeds a metric for # of items opened in the last 7 days. Friday morning it said 118, and by Friday afternoon it said 81. I understand that the formula updated, but my stakeholders feel the data must be unreliable. This is one of the earlier formulas I wrote, I can't remember why it has the minus 1.

=NETDAYS([Approved Date]@row, TODAY()) - 1


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That is partially correct. If the sheet is not ACTIVATED then then TODAY function will not update. You don't necessarily need to open a sheet though to activate it. You could use a form or update request submission, cell links, formulas with cross sheet references, or...

    Insert a date type column and then create a Record a Date automation. Have it set to run daily at 12:00am and populate in the new date type column. This will activate the sheet every morning. Smartsheet says that it could take up to 15 minutes to run automations from the time of trigger, but I have never seen it go more than 9 minutes. Generally speaking I believe my current average is less than 2 minutes. So every day between 12:00 and 12:15 am your automation will run which will update your TODAY function.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!