Counting working days in a week

Brian W

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.

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

Brian W

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