Number of WorkDays & Weekend Days in each month

[ricafonyat]

Hello !

What formula should I use in cells D4:O5 to display the number of working days and weekend days considering each of the months in cells D3:O3?

In my case only Fridays and saturdays are weekend days.

Many Thanks in advance
Best Regards

 

[Cubist]

Try

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3				Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
4			Workdays	23	21	21	22	22	21	23	21	22	23	20	23
5			Weekend days	8	8	10	8	9	9	8	10	8	8	10	8
Sheet8

Cell Formulas
Range	Formula
E3:O3	E3	=EDATE(D3,SEQUENCE(,11))
D4:O4	D4	=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),7)
D5:O5	D5	=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),"1111001")
Dynamic array formulas.

 

[Dave Patton]

ignore Consistent with above

 

[Rick Rothstein]

Try

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3				Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
4			Workdays	23	21	21	22	22	21	23	21	22	23	20	23
5			Weekend days	8	8	10	8	9	9	8	10	8	8	10	8
Sheet8

Cell Formulas
Range	Formula
E3:O3	E3	=EDATE(D3,SEQUENCE(,11))
D4:O4	D4	=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),7)
D5:O5	D5	=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),"1111001")
Dynamic array formulas.

Instead of the two separate formulas in cells D4 and D5, you can use this single formula in cell D4 to produce both rows of values...

=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),{"0000110";"1111001"})

Note: The semi-colon inside the array constant is my locales "next row" delimiter for array constants.

 

[Cubist]

Instead of the two separate formulas in cells D4 and D5, you can use this single formula in cell D4 to produce both rows of values...

=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),{"0000110";"1111001"})

Note: The semi-colon inside the array constant is my locales "next row" delimiter for array constants.

I did not think about that, but passing a 7 also works in the array.

=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),{7;"1111001"})

 

[Rick Rothstein]

I did not think about that, but passing a 7 also works in the array.

=NETWORKDAYS.INTL(+D3:O3,EOMONTH(+D3:O3,0),{7;"1111001"})

And I did not think about doing that... I just figured the array had to use the same pattern mode and it never crossed my mind to check if that was so.