Counting working days in a week

Brian W
Brian W ✭✭
edited 12/09/19 in Smartsheet Basics

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(Start@row >= DATE(2019, 6, 10), Finish@row <= DATE(2019, 6, 14)), NETWORKDAYS(Start@row, Finish@row), IF(AND(Start@row < DATE(2019, 6, 10), Finish@row > DATE(2019, 6, 14)), NETWORKDAYS(DATE(2019, 6, 10), DATE(2019, 6, 14)), IF(Start@row > DATE(2019, 6, 14), 0, IF(Finish@row < DATE(2019, 6, 10), 0, IF(Finish@row <= DATE(2019, 6, 14), 5 - (DATE(2019, 6, 14) - Finish@row), IF(Start@row >= DATE(2019, 6, 10), 5 - (Start@row - 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

  • sean59916
    sean59916 ✭✭✭

    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($Start@row - Start$1, 0) - MAX(Finish$1 - $Finish@row, 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 W
    Brian 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.