=NETDAYS formula updating in the afternoon

Erica Cole · 05/17/21

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 · 05/18/21

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 · 05/17/21

Have you tried changing the time zone for you account?

Erica Cole · 05/17/21

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

Bassam Khalil · 05/18/21

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 · 05/18/21

Paul Newcome · 05/18/21

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 · 05/18/21

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 · 05/18/21

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.

=NETDAYS formula updating in the afternoon

Best Answer

Answers

Help Article Resources

Categories