Enter start and end dates, divide total days by months (minus holidays)?

0
votes

Using Excel 2013, I am attempting to divide total numbers of days in a period by month, excluding specific holidays and weekends. The total numbers would be calculated from user-entered start and end dates. I am able to calculate the total number of days for the period using NETWORKDAYS.

=NETWORKDAYS(A3, B3, Holidays!$A$2:$A$50)

I am also able to calculate the total of number of days for each month using NETWORKDAYS. Below is my example for January.

=NETWORKDAYS(DATE(2014,1,1), DATE(2014,1,31), Holidays!$A$2:$A$50)

I have yet to structure arguments that account for the user-entered dates by month. To illustrate, here's is a screenshot of what I would like to accomplish, but with formulas: enter image description here

I have hundreds of cases to enter, and am hoping I can calculate the months with start and end dates rather than entering them manually.

Any suggestions? Thanks for your time.

1
excelexcel-formula
they all have the same columns? – Fabricator
I would ask the same question as @Fabricator, will the columns always be in the same order? (i.e. Aug -> Jun) – StephenH
Yes. They are based on an academic calendar. – slowerdawn

1 Answers

0
votes

The below appears to be working for me, though it's kind of gnarly. As a note, to make this work, each of your column headers should be the first of the month (like Aug 1, 2013). But you can format these dates so they just show up as the month abbreviations.

enter image description here

Formula to put in D3 and then pull across and down:

=IF((IF(AND($A3<D$1,$B3>(E$1-1)),NETWORKDAYS(D$1,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3>(E$1-1)),NETWORKDAYS($A3,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3<D$1,$B3<(E$1-1)),NETWORKDAYS(D$1,$B3,Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3<(E$1-1)),NETWORKDAYS($A3,$B3,Holidays!$A$2:$A$50))))))<0,0,(IF(AND($A3<D$1,$B3>(E$1-1)),NETWORKDAYS(D$1,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3>(E$1-1)),NETWORKDAYS($A3,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3<D$1,$B3<(E$1-1)),NETWORKDAYS(D$1,$B3,Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3<(E$1-1)),NETWORKDAYS($A3,$B3,Holidays!$A$2:$A$50)))))))