Counting working days in a week

Brian WBrian W
edited 12/09/19 in Using Smartsheet
05/31/19 Edited 12/09/19

Hi everyone. I'm trying to come up with the best way to count the number of days that a project is being worked on in a particular week. You can see in my screenshot that ProductiveDays is counting the working days from 6/10 to 6/14.

I've come up with a rather cumbersome formula that works and only requires that I manually change the dates. I could easily have the dates linked to cells so I wouldn't have to copy and paste a new formula each week.

=IF(AND([email protected] >= DATE(2019, 6, 10), [email protected] <= DATE(2019, 6, 14)), NETWORKDAYS([email protected], [email protected]), IF(AND([email protected] < DATE(2019, 6, 10), [email protected] > DATE(2019, 6, 14)), NETWORKDAYS(DATE(2019, 6, 10), DATE(2019, 6, 14)), IF([email protected] > DATE(2019, 6, 14), 0, IF([email protected] < DATE(2019, 6, 10), 0, IF([email protected] <= DATE(2019, 6, 14), 5 - (DATE(2019, 6, 14) - [email protected]), IF([email protected] >= DATE(2019, 6, 10), 5 - ([email protected] - DATE(2019, 6, 10))))))))

Is there an easier way to do this? I've been racking my brain trying to come up with a solution and this is the best I could do.

Screen Shot 2019-05-31 at 4.22.41 PM.png

Comments

  • Hey Brian - 

    This is how i would go about it: 

    1. Add a row and insert the start/Finish Date

    2. Use the following formula for the project rows:

    =MAX(NETWORKDAYS(Start$1, Finish$1) - MAX([email protected] - Start$1, 0) - MAX(Finish$1 - [email protected], 0), 0)

    To understand the formula if we look at the first row:

    • Network Days between Start,Finish = 5
    • Difference between Task Start, Start = -7. The max statement here returns 0.
    • Difference between Finish, Task Finish = -7. The max statement here returns 0.

    So, the formula becomes:

    =MAX((5 - 0 - 0), 0) or MAX(5,0) = 5

     

    I hope this helps you out?  Screenshots attached of the results and the expanded explanation.

    Regards, 



    Sean

    2019-06-01_11-38-58.jpg

    2019-06-01_11-48-05.jpg

  • Brian WBrian W
    edited 06/03/19

    Thanks, Sean. I haven't quite wrapped my brain around the math yet, but it works great and I really appreciate your detailed explanation.

Sign In or Register to comment.