Calculate number of working days (excluding holydays) month by month

[cbprado]

This is to calculate # of workdays per month in a project

A2 and B2 I´ll have start and end dates, c1 - q1 I´ll have the months (jan-dec)

holydays will be on a named array sized 2cx8r.

thanx

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	2/25/2019	10/15/2019	0	3	20	21	22	20	23	21	21	10	0	0
3
4
5	1	2/5/2019
6	2	2/27/2019
7	3	3/1/2019
8	4	4/9/2019
9	5	5/6/2019
10	6	8/8/2019
11	7	10/11/2019
12	8	12/12/2019

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =MAX(0,NETWORKDAYS(MAX($A$2,C1),MIN($B$2,EOMONTH(C1,0)),INDEX(HolyDays,0,2))) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To HolyDays =Sheet3!$A$5:$B$12 <tbody> </tbody>

<tbody>
</tbody>

Note that the values in C1:N1 are actual dates, the 1st of each month, and I use a custom format of mmm to just show the month. Range A5:B12 is the HolyDays range, you can of course put it anywhere. If you have different weekend requirements, you can use NETWORKDAYS.INTL instead.

 

[cbprado]

You guys are amazing. Thank you!

 

[Eric W]

Happy to help!