Completion Date vs. Due Date based on Associate

Jaclyn Royer

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!!

Stefan

Hi @Jaclyn Royer,

have a look at the NETWORKDAYS formula:

https://help.smartsheet.com/function/networkdays

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

use

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

Hope this helps

Stefan

Jaclyn Royer

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?

Stefan

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

Jaclyn Royer

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!

Stefan

Hi @Jaclyn Royer;

have a look here:

https://help.smartsheet.com/articles/765755-modifying-project-settings

Hope this helps

Stefan

Jaclyn Royer

Thank you!