Working Days Calculation

[nymyth]

Hi everyone,

I am in need of some assistance. I am trying to determine how many working days in a given month within a given year are available. I know we can use the networkdays function, however I have some constraints that i need help working around.

I have 2 rows, the first row reads the year, the second row reads "jan, feb, mar..." and that's it. From this I am trying to write a formula that will extract the working days in that month in that year. So, in Jan 2018 I need it to show 23, for Feb 2018 it should show 20 etc.

I started with something like this:
=NETWORKDAYS((MONTH(--("01-"&L7))),EOMONTH((MONTH(--("01-"&L7))),0))

Where L7 is the cell that reads "Jan", however this gives me 22 and doesn't take the year into consideration. Any help is appreciated. Thank you.

 

[Dave Patton]

Several suggestions for your consideration.
If you use USA Regional Settings, you may have to reverse the month part ie =(1&B2)+0 etc to (B2&1)+0

Excel 2010
	A	B	C	D	E	F
1	Year	Month
2	2018	Jan	1-Jan-18	23	23	23
3	2017	Jan	1-Jan-17	22		22
1d

Cell Formulas
Range	Formula
C3	=DATE(A3,MONTH((1&B3)+0),1)
C2	=(1&B2)+0
D3	=NETWORKDAYS(C3,EOMONTH(C3,0))
D2	=NETWORKDAYS(C2,EOMONTH(C2,0))
E2	=NETWORKDAYS((1&B2)+0,EOMONTH((1&B2)+0,0))
F2	=NETWORKDAYS.INTL(DATE(A2,MONTH((1&B2)+0),1),DATE(A2,MONTH((1&B2)+0)+1,0))
F3	=NETWORKDAYS.INTL(DATE(A3,MONTH((1&B3)+0),1),DATE(A3,MONTH((1&B3)+0)+1,0))

 

[nymyth]

so I adjusted to this:
=NETWORKDAYS(DATEVALUE(L9&"1"),EOMONTH(DATEVALUE(L9&"1"),0),0)

Where L9 = "Jan" - this gives me 23 days like i expected, but only for the current year. How can I update this to automatically pull for Jan 2019?

 

[nymyth]

Several suggestions for your consideration.
If you use USA Regional Settings, you may have to reverse the month part ie =(1&B2)+0 etc to (B2&1)+0



Excel 2010

	A	B	C	D	E	F
1	Year	Month
2	2018	Jan	1-Jan-18	23	23	23
3	2017	Jan	1-Jan-17	22		22

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1d​

Worksheet Formulas Cell Formula C3 =DATE(A3,MONTH((1&B3)+0),1) D3 =NETWORKDAYS(C3,EOMONTH(C3,0)) C2 =(1&B2)+0 D2 =NETWORKDAYS(C2,EOMONTH(C2,0)) E2 =NETWORKDAYS((1&B2)+0,EOMONTH((1&B2)+0,0)) F2 =NETWORKDAYS.INTL(DATE(A2,MONTH((1&B2)+0),1),DATE(A2,MONTH((1&B2)+0)+1,0)) F3 =NETWORKDAYS.INTL(DATE(A3,MONTH((1&B3)+0),1),DATE(A3,MONTH((1&B3)+0)+1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

AWESOME - I used this one and works like a charm: =NETWORKDAYS.INTL(DATE(A2,MONTH((1&B2)+0),1),DATE(A2,MONTH((1&B2)+0)+1,0))