Formula to Determine How Many Days From a Specified Date Range Appear in a Different Date Range?

[Gadgetgav]

Hi All,

I have a list of events with start date and end dates (sometimes these are single day events) but I need to know how many working days appear in a certain month.

For example someone has taken a holiday from the 29th of January to the 5th of February 2021 this is 6 working days but only 5 of those are in February.

Is there a formula I can apply that will take the start date and end date of the event then calculate how many working days in that range are from each month (so in the above example it would give me 1 for Jan, 5 for Feb, 0 for Mar etc.)?

Thanks in advance (please ask if my question doesn't make sense)
Gav

 

[JamesCanale]

Do you have 365? This takes EOMONTH. If not, you can replace eomonth with a somewhat more convoluted formula.

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021
2	1/5/2021	1/10/2021	4	0	0	0	0	0	0	0	0	0	0	0
3	1/8/2021	1/8/2021	1	0	0	0	0	0	0	0	0	0	0	0
4	1/29/2021	2/4/2021	1	4	0	0	0	0	0	0	0	0	0	0
5	2/24/2021	6/3/2021	0	3	23	22	21	3	0	0	0	0	0	0
Sheet45

Cell Formulas
Range	Formula
D1:N1	D1	=EOMONTH(C1,0)+1
C2:N5	C2	=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN($B2,EOMONTH(C$1,0))))

For older versions of excel:

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
7	Start	End	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021
8	1/5/2021	1/10/2021	4	0	0	0	0	0	0	0	0	0	0	0
9	1/8/2021	1/8/2021	1	0	0	0	0	0	0	0	0	0	0	0
10	1/29/2021	2/4/2021	1	4	0	0	0	0	0	0	0	0	0	0
11	2/24/2021	6/3/2021	0	3	23	22	21	3	0	0	0	0	0	0
Sheet45

Cell Formulas
Range	Formula
C8:N11	C8	=MAX(0,NETWORKDAYS(MAX(C$1,$A8),MIN($B8,(DATE(YEAR((DATE(YEAR(C$7),MONTH(C$7),1)+32)),MONTH((DATE(YEAR(C$7),MONTH(C$7),1)+32)),1)-1))))

 

[Gadgetgav]

That worked brilliantly, thank you