Calculating the Total working days of each month without start date and end date.

[Zee996]

In cell A1 I have month columns and I am looking for a formula that will calculate the total working days in a particular month of that year. There is no start date or end date just the month name.
Like - Aug, Sept, Oct..so on.
Is it possible to get the total working days excluding Saturday & Sunday?
Thank you!

		​

 

[Dave Patton]

T202209a.xlsm
	A	B
1	Aug	23
2	Sep	22
3
2c

Cell Formulas
Range	Formula
B1:B2	B1	=LET(d,--(1&A1),NETWORKDAYS.INTL(d,EOMONTH(d,0),1))

N.B. My system's Regional Settings are dd-mm-yyyy. If the suggestion doesn't work, try reversing the 1&A1 part.

 

[Zee996]

T202209a.xlsm
	A	B
1	Aug	23
2	Sep	22
3
2c

Cell Formulas
Range	Formula
B1:B2	B1	=LET(d,--(1&A1),NETWORKDAYS.INTL(d,EOMONTH(d,0),1))

N.B. My system's Regional Settings are dd-mm-yyyy. If the suggestion doesn't work, try reversing the 1&A1 part.

Thank you so much for this!

Just one question. Will the days update if the year on the computer changes?

 

[Zee996]

Is there a way to update the formula with the Year Column? As the Year changes the working days of 2022 will changes into 2023. I don't want that to change.
Thank you for your help!

 

[Dave Patton]

You can specify the year.

T202209a.xlsm
	A	B	C
4	Jan	2023	22
5	Jan	2022	21
2c

Cell Formulas
Range	Formula
C4:C5	C4	=LET(d,DATE(B4, MONTH(A4&1),1),NETWORKDAYS.INTL(d,EOMONTH(d,0),1))

 

[Zee996]

You can specify the year.

T202209a.xlsm
	A	B	C
4	Jan	2023	22
5	Jan	2022	21
2c

Cell Formulas
Range	Formula
C4:C5	C4	=LET(d,DATE(B4, MONTH(A4&1),1),NETWORKDAYS.INTL(d,EOMONTH(d,0),1))

Thank you so much for your help!