=NETDAYS formula updating in the afternoon

Erica Cole

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

Paul Newcome

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.

Chris Wolfe

Have you tried changing the time zone for you account?

Erica Cole

My personal setting are aligned with my time zone. Could it be the enterprise time zone setting?

Bassam Khalil

Hi @Erica Cole

Hope you are fine, could you please share a screenshot for the Time Zoon setting. because your formula is scorrect.

Erica Cole

Paul Newcome

Have you tried opening the sheet prior to 3pm to "force" the TODAY function to update? If it is a matter of the TODAY function not updating, there are a number of workarounds for this.

Erica Cole

Ahhh, are you saying it updates when you access the sheet? Meaning if I update the sheet Monday, and don't access it again until Thursday, it will not update my "Aging" column daily?

Paul Newcome

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.