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
Year	Month
Jan
Jan

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2018[/TD]

[TD="align: right"]1-Jan-18[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2017[/TD]

[TD="align: right"]1-Jan-17[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]

</tbody>

1d​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=DATE(A3,MONTH((1&B3)+0),1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=NETWORKDAYS(C3,EOMONTH(C3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=(1&B2)+0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=NETWORKDAYS(C2,EOMONTH(C2,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=NETWORKDAYS((1&B2)+0,EOMONTH((1&B2)+0,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=NETWORKDAYS.INTL(DATE(A2,MONTH((1&B2)+0),1),DATE(A2,MONTH((1&B2)+0)+1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=NETWORKDAYS.INTL(DATE(A3,MONTH((1&B3)+0),1),DATE(A3,MONTH((1&B3)+0)+1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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))