Completion Date vs. Due Date based on Associate

09/16/20
Answered - Pending Review

Hello -

I am trying to count the work days in between a due date vs. completion date by reviewer (not including weekends and holidays). Is this possible?

To compare the due date column date to the completion date and then count the work days in between those two dates for specific reviewers?

Right now I added a "work days between" column and calculated the days in between the due date and the completed date column using =[Due Date]528 - [Completed Date]528. Is there a way to not count weekends/holidays in the work days in this calculation?

I then calculate the work days between using the formula below

=SUMIFS([Work Days Between]:[Work Days Between], Reviewer:Reviewer, "Name") / COUNTIF(Reviewer:Reviewer, "Name")


Thank you in advance!!

Answers

  • StefanStefan ✭✭✭

    Hi @Jaclyn Royer,

    have a look at the NETWORKDAYS formula:

    So instead of =[Due Date]528 - [Completed Date]528

    use

    =NETWORKDAYS([Due Date]528, [Completed Date]528)

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thank you! Is there a reason why Smartsheets counts "1" work day between the due date and completed date if they are the same value? So if the due date was 9/4 and the completed date was 9/4 it is counting 1 not 0?

  • StefanStefan ✭✭✭

    Hi @Jaclyn Royer,

    in your project settings one day is probably set to be 8 hours and 8 hours fit into one 24 hours timeframe starting 00:00 and ending 23:59,59.

    That's standard in most tools.

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Ok thank you for the feedback! I do not see the option of project settings in the 'Personal Settings' dropdown of Smartsheets. Is this a specific setting in the Smartsheet you are using? Thank you!

  • StefanStefan ✭✭✭

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thank you!

Sign In or Register to comment.