Networkdays, Error?

Networkdays, Error?

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?


Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

  • 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 NewcomePaul 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?

  • 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)



  • 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 NewcomePaul 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.

  • 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.

Sign In or Register to comment.