Business days in time period by months

[nenandi]

How do I recalculate how many days belong in a month if it has a start date and an end date in the column for each month, do I need to create auxiliary columns for EOMONTH so that I can only calculate them on a business day?

 

[FormR]

Hi, here's one option you can try.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	19/07/2021	05/08/2021	0	0	0	0	0	0	10	4	0	0	0	0
3	07/06/2021	25/06/2021	0	0	0	0	0	15	0	0	0	0	0	0
4	30/09/2021	08/10/2021	0	0	0	0	0	0	0	0	1	6	0	0
5	29/10/2021	01/12/2021	0	0	0	0	0	0	0	0	0	1	22	1
Sheet1

Cell Formulas
Range	Formula
C2:N5	C2	=MAX(NETWORKDAYS(MAX($A2,DATE(2021,MONTH(1&C$1),1)),MIN($B2,DATE(2021,MONTH(1&C$1)+1,0))),0)

 

[jasonb75]

This will do it, custom format the dates in row 1 as mmm or otherwise desired to show month names instead of dates.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021
2	19/07/2021	05/08/2021							10	4
Sheet1

Cell Formulas
Range	Formula
C2:N2	C2	=IFERROR(--TEXT(NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))),"0;;"),"")