Count number of networkdays for each month in a multi month period

**dreddster** · 04-25-2013, 08:44 AM

Hey guys,

I have this table that has a column for starting date, end date, and number of working days. All is good so far.
Now I have another separate 12 columns for each month. I want to count in each of these 12 columns the number of networkdays that are in the abovementioned period.

The reason I need this for is:
I want to calculate the number of days that each person has worked on this project for each month. For example the project took 20 days across two months, which include 15 days in January and 5 in February.

How would I go about doing this?

Thank you very much in advance!

**etaf** · 04-25-2013, 09:40 AM

can we see a sample sheet of the data
so we can see how you have laid it out
and where you want the result

assume it started mid Jan and finished mid Dec

then that would be part jan - feb, mar,apr,may,jun,jul,aug,sep,oct,nov and part dec - how will the result be presented ?
in each of the columns

it can be done with a lot of Ifs - and using EOmonth() but sure some one will help with a better solution - but would like to see the sample sheet

**daddylonglegs** · 04-25-2013, 09:53 AM

If you have start date in A2, end date in B2 and the number of working days in C2 then try this to get number of working days in each month. List the 1st of each relevant month in D1 across, e.g. 1-Jan-2013 in D1, 1-Feb-2013 in E1 etc. and then use this formula in D2 copied across and down

=MAX(0,NETWORKDAYS(MAX($A2,D$1),MIN($B2,EOMONTH(D$1,0))))

You will get zeroes in all months which don't intersect with your defined period

If you are using Excel 2003 you will need Analysis ToolPak enabled to use EOMONTH and NETWORKDAYS functions

see example attached, I formatted the output data so that zeroes display as blanks

**dreddster** · 04-25-2013, 06:42 PM

This is one really awesome macro, that works like a charm. Exactly what I need!

Thank you very much daddylonglegs !

Now I will have to make one tough calculation.
I adjusted your table to reflect what I want to achieve:

There are several people working on each project. Moreover each project is worked on twice a year (Thus two starting/end dates). On each month column the two rows correspond to the first period the project is worked on (upper row), and the second period the project is worked on (lower row).
On Sheet 2 called HR, there is a table for each employee and how many days of each month has he worked on all projects.
So what I want to achieve is for Excel to match the name of an employee if it exists as one that has worked on a project, then copy the sum of days he has worked for each month to Sheet HR on the respective month.

So for John Doe on Sheet HR on March it should say 41 days.

I hope it is not some overcomplicated formula that must be used. If something is not clear enough, let me know to clarify if further.
workdays in month.xls

**daddylonglegs** · 04-26-2013, 07:11 PM

Originally Posted by dreddster

So for John Doe on Sheet HR on March it should say 41 days.

Aren't 15 of those hours for Jack Jonsson? Try this formula in HR B2 copied across and down

=SUMPRODUCT(Sheet1!$I$2:$T$10,(Sheet1!$I$1:$T$1=B$1)*(Sheet1!$B$2:$B$10=$A2))

**dreddster** · 05-08-2013, 09:39 AM

I apologize for the delay, we had some holidays over here

This command SUMPRODUCT is awesome, and your formula is working!

.
However the table I was creating got a different look with some more options on it, that I guess are messing with it.
What I achieved is to go over the first name of the first project and sum the days for the first and the second leg. But I was able to make it work only for 1 cell on names (D4 and H4) and only for project 1. If I try to include more name fields (for example D4:D43 and H4:H43) it gives me an error. Moreover I get error if I increase the range for multiple projects. Also I have to make it look across the projects on each department.

employeesV2.xlsx
The table is actually composed of 5 departments, ~100 projects on each dept and 132 employees.

Is this possible with a formula or something more advanced is needed?

Moreover there is something else regarding planning that I am wondering if it is possible to achieve using formula. If an employee is put on a project, then on HR sheet Row 3, 6 etc. the respective cells for the respective weeks will be filled with the short name of the project from column C of each dept project. I will then make a conditional formatting if the cell has a text to color it. The idea is to colorize the schedule on each employee and find out easily who is available and at what time and on which project he is assigned to.

Thank you in advance!

**dreddster** · 05-10-2013, 11:12 AM

Actually I managed to make it look through 4 names for each leg on each of the five sheets for 1 project. But the function looks like this:
(SUMPRODUCT(Dept1!$L$6:$W$6;(Dept1!$L$2:$W$2=E$1)*(Dept1!$D$4=$A3))+
(SUMPRODUCT(Dept1!$L$7:$W$7;(Dept1!$L$2:$W$2=E$1)*(Dept1!$H$4=$A3))))+
(SUMPRODUCT(Dept2!$L$6:$W$6;(Dept2!$L$2:$W$2=E$1)*(Dept2!$D$4=$A3))+
(SUMPRODUCT(Dept2!$L$7:$W$7;(Dept2!$L$2:$W$2=E$1)*(Dept2!$H$4=$A3))))+
(SUMPRODUCT(Dept3!$L$6:$W$6;(Dept3!$L$2:$W$2=E$1)*(Dept3!$D$4=$A3))+
(SUMPRODUCT(Dept3!$L$7:$W$7;(Dept3!$L$2:$W$2=E$1)*(Dept3!$H$4=$A3))))+
(SUMPRODUCT(Dept4!$L$6:$W$6;(Dept4!$L$2:$W$2=E$1)*(Dept4!$D$4=$A3))+
(SUMPRODUCT(Dept4!$L$7:$W$7;(Dept4!$L$2:$W$2=E$1)*(Dept4!$H$4=$A3))))+
(SUMPRODUCT(Dept5!$L$6:$W$6;(Dept5!$L$2:$W$2=E$1)*(Dept5!$D$4=$A3))+
(SUMPRODUCT(Dept5!$L$7:$W$7;(Dept5!$L$2:$W$2=E$1)*(Dept5!$H$4=$A3))))+
(SUMPRODUCT(Dept1!$L$6:$W$6;(Dept1!$L$2:$W$2=E$1)*(Dept1!$D$5=$A3))+
(SUMPRODUCT(Dept1!$L$7:$W$7;(Dept1!$L$2:$W$2=E$1)*(Dept1!$H$5=$A3))))+
(SUMPRODUCT(Dept2!$L$6:$W$6;(Dept2!$L$2:$W$2=E$1)*(Dept2!$D$5=$A3))+
(SUMPRODUCT(Dept2!$L$7:$W$7;(Dept2!$L$2:$W$2=E$1)*(Dept2!$H$5=$A3))))+
(SUMPRODUCT(Dept3!$L$6:$W$6;(Dept3!$L$2:$W$2=E$1)*(Dept3!$D$5=$A3))+
(SUMPRODUCT(Dept3!$L$7:$W$7;(Dept3!$L$2:$W$2=E$1)*(Dept3!$H$5=$A3))))+
(SUMPRODUCT(Dept4!$L$6:$W$6;(Dept4!$L$2:$W$2=E$1)*(Dept4!$D$5=$A3))+
(SUMPRODUCT(Dept4!$L$7:$W$7;(Dept4!$L$2:$W$2=E$1)*(Dept4!$H$5=$A3))))+
(SUMPRODUCT(Dept5!$L$6:$W$6;(Dept5!$L$2:$W$2=E$1)*(Dept5!$D$5=$A3))+
(SUMPRODUCT(Dept5!$L$7:$W$7;(Dept5!$L$2:$W$2=E$1)*(Dept5!$H$5=$A3))))+
(SUMPRODUCT(Dept1!$L$6:$W$6;(Dept1!$L$2:$W$2=E$1)*(Dept1!$D$6=$A3))+
(SUMPRODUCT(Dept1!$L$7:$W$7;(Dept1!$L$2:$W$2=E$1)*(Dept1!$H$6=$A3))))+
(SUMPRODUCT(Dept2!$L$6:$W$6;(Dept2!$L$2:$W$2=E$1)*(Dept2!$D$6=$A3))+
(SUMPRODUCT(Dept2!$L$7:$W$7;(Dept2!$L$2:$W$2=E$1)*(Dept2!$H$6=$A3))))+
(SUMPRODUCT(Dept3!$L$6:$W$6;(Dept3!$L$2:$W$2=E$1)*(Dept3!$D$6=$A3))+
(SUMPRODUCT(Dept3!$L$7:$W$7;(Dept3!$L$2:$W$2=E$1)*(Dept3!$H$6=$A3))))+
(SUMPRODUCT(Dept4!$L$6:$W$6;(Dept4!$L$2:$W$2=E$1)*(Dept4!$D$6=$A3))+
(SUMPRODUCT(Dept4!$L$7:$W$7;(Dept4!$L$2:$W$2=E$1)*(Dept4!$H$6=$A3))))+
(SUMPRODUCT(Dept5!$L$6:$W$6;(Dept5!$L$2:$W$2=E$1)*(Dept5!$D$6=$A3))+
(SUMPRODUCT(Dept5!$L$7:$W$7;(Dept5!$L$2:$W$2=E$1)*(Dept5!$H$6=$A3))))+
(SUMPRODUCT(Dept1!$L$6:$W$6;(Dept1!$L$2:$W$2=E$1)*(Dept1!$D$7=$A3))+
(SUMPRODUCT(Dept1!$L$7:$W$7;(Dept1!$L$2:$W$2=E$1)*(Dept1!$H$7=$A3))))+
(SUMPRODUCT(Dept2!$L$6:$W$6;(Dept2!$L$2:$W$2=E$1)*(Dept2!$D$7=$A3))+
(SUMPRODUCT(Dept2!$L$7:$W$7;(Dept2!$L$2:$W$2=E$1)*(Dept2!$H$7=$A3))))+
(SUMPRODUCT(Dept3!$L$6:$W$6;(Dept3!$L$2:$W$2=E$1)*(Dept3!$D$7=$A3))+
(SUMPRODUCT(Dept3!$L$7:$W$7;(Dept3!$L$2:$W$2=E$1)*(Dept3!$H$7=$A3))))+
(SUMPRODUCT(Dept4!$L$6:$W$6;(Dept4!$L$2:$W$2=E$1)*(Dept4!$D$7=$A3))+
(SUMPRODUCT(Dept4!$L$7:$W$7;(Dept4!$L$2:$W$2=E$1)*(Dept4!$H$7=$A3))))+
(SUMPRODUCT(Dept5!$L$6:$W$6;(Dept5!$L$2:$W$2=E$1)*(Dept5!$D$7=$A3))+
(SUMPRODUCT(Dept5!$L$7:$W$7;(Dept5!$L$2:$W$2=E$1)*(Dept5!$H$7=$A3))))

And the total number of projects is close to 500, which means this formula should be 500 times longer. What is the best way to make it shorter?