Networkdays, Error?

Jason P

I'm running =NETWORKDAYS([Date Submitted]22, (TODAY())) which counts how long a design request has been in the Dept. In some cases the count is correct, based on today as 19th May 2020, yet in other cells it's out by a day or more? I've confirmed the formula is the same in the column. Help?

Paul Newcome

Happy to help. 👍️

I am not sure what is on Smartsheet's roadmap, but I have seen this come up a few other times in the past. It seems to come in waves. Feel free to Submit a Product Enhancement Request when you are able to make the suggestion.

Please don't forget to mark the most appropriate response(s) as "helpful". This will flag the response(s) as the "Best Answer(s)" which will mark the post as "Answered" and make it easier for people searching for a similar solution to find.

Paul Newcome

Try separating the date from the timestamp and see if that makes a difference.

=NETWORKDAYS(DATEONLY([Date Submitted]@row), TODAY())

Jason P

Morning Paul,

No luck, column is a system generated "Created (Date)" as sheet is form fed, think this is the reason DATEONLY isn't recognised. Is there a way to achieve a date stamp outside the system generated type?

Cheers

Paul Newcome

The DATEONLY is specifically designed for the date/time stamp columns to separate the date from the column.

It may also be a timezone thing. I recently helped someone else who was having it registered as the next day for any times after 5:00pm.

Could you take a look at the times and see if there could be some consistency to support that theory?

Jason P

Morning Paul,

I was looking at the clock time too recalling 'SS' times on automated workflows thinking some link there. As it is I have a examples in the attached.

In the first column we have 5 jobs all in from 20th and on the far right cells where the formula sits, you can see all but 1 is at 2 days - (today is 21st 6.15 AM) above that for 19th same occurrence.

This would suggest 3 PM is a trigger for a new day and included in the count? entries before 3 PM on the day are counted as 1 day, then from 3 PM a new clock day starts and again is counted, this would explain examples below 4 & 3 for the 19th and 2 & 3 for the 20th. Thoughts?

Still does not explain the DATEONLY not working.

Have confirmed system clock is correct, our servers are in Melbourne (Aust) and Smartsheet personal settings set at (GMT+10) Aust NSW, regional preferences to English (Aust)

Jason P

Paul,

In the first column we have 5 jobs all in from 20th and on the far right cells where the formula sits, you can see all but 1 is at 2 days - (today is 21st 6.15 AM) above that for 19th same occurrence.

This would suggest 3 PM is a trigger for a new day and included in the count? entries before 3 PM on the day are counted as 1 day, then from 3 PM a new clock day starts and again is counted, this would explain examples below 4 & 3 for the 19th and 2 & 3 for the 20th. Thoughts?

My formula did not carry to the last entry, on correction the 3 changed to 2. On submitting a new request via the form it came in as 2 days in duration. - somewhere here is the issue.

Cheers.

Paul Newcome

It looks like it is going to be a time zone thing (on the back-end/not on your end). Let's try this...

=NETWORKDAYS(DATEONLY([Date Submitted]@row) + IF(AND(VALUE(MID([Date Submitted]@row, 10, FIND(":", [Date Submitted]@row) - 10)) >= 3, CONTAINS("P", [Date Submitted]@row)), -1), TODAY())

HERE is a link to a thread with a similar problem.

Jason P

Nailed it... wow... and wow again after I have read the formula 5 times. I've looked at 6 instances and confirm all number of days in the sheet are correct, accounts for submission date as 1 and no weekend dates included.

Think Smartsheet will update this bug and have times/dates run of a users system clock? seems a over complicated fx for something so simple.

Many thanks again Paul.

Paul Newcome

Happy to help. 👍️

I am not sure what is on Smartsheet's roadmap, but I have seen this come up a few other times in the past. It seems to come in waves. Feel free to Submit a Product Enhancement Request when you are able to make the suggestion.

Please don't forget to mark the most appropriate response(s) as "helpful". This will flag the response(s) as the "Best Answer(s)" which will mark the post as "Answered" and make it easier for people searching for a similar solution to find.

Jason P

Hi @Paul Newcome

Hope you are well.

Old post now but I recently had call to use NETWORKDAYS & DATEONLY. again there was an error in results so I contacted Smartsheet, their response.

"Upon checking on our resources, using a Dateonly function will convert and display time based on GMT and not on your time zone settings. If you use DATEONLY on a System Date column, the date produced by the function may be different from your local time zone. While using a Networkdays function will count Saturday and Sunday as non-working days. If dependencies are enabled on your sheet, you can customize the non-working days, and the formulas will use your settings in calculations."

Perhaps you already know this but just in case you assist another

Paul Newcome

@Jason P Thanks for the update. The challenge with this is that it requires the Dependencies to be used and is also assuming you want something submitted after your "working hours" to be counted as the next day.

Jason P

Up for that challenge? In sheets where I use the formula I don't require dependencies, for me it was more just to set KPI's for the team and a service level to other teams viewing the data.

Have a good one Paul.

Paul Newcome

@Jason P Same here. I had to develop the workaround because I wasn't using dependencies.