Completion Date vs. Due Date based on Associate
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
-
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?
-
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!
-
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!