List Network Days

[msk7777]

Hey everyone...having difficulty finding the answer to this one. Could be looking wrong. All I am trying to do is list all net work days for a month. In cell A1 I have a drop down list with all months listed. I already have a NETWORKDAYS list to take into account our paid holidays. I want to be able to select the month and it automatically show the net work days for that month in cells A7:A29.

What would be the best set up for this?

Thanks for any help!

 

[Ron Coderre]

With
A1: containing a month name (from a dropdown list)...eg JUN

This regular formula begins the list of workdays:

B1: =WORKDAY(($A$1&"-1")-1,1,HolidayList)

And this regular formula, copied down through B31, continues the list:

EDITED....B2 formula replaced with a better one:
B2: =IF(B1="","",IF(TEXT(WORKDAY(B1,1,$M$2:$M$15),"mmm")<>$A$1,"",WORKDAY(B1,1,$M$2:$M$15)))
<?XML:NAMESPACE PREFIX = NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2 /><NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>

</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Format those cells as dates.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p></NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Note_1: HolidayList refers to whatever range you used to identify holidays.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Note_2: the space before and afte the < symbol are there to prevent this website from interpreting the symbol as an HTML tag.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p></NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>
Is that something you can work with?
</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>

 

[AlphaFrog]

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:190px;" /><col style="width:22px;" /><col style="width:76.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:26px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >March</td><td > </td><td style="font-weight:bold; ">Holidays</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:right; ">1/1/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">3/9/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">3/17/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">4/22/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">5/30/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">Tuesday, March 01, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">Wednesday, March 02, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">Thursday, March 03, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">Friday, March 04, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">Monday, March 07, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">Tuesday, March 08, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">Thursday, March 10, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">Friday, March 11, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">Monday, March 14, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">Tuesday, March 15, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">Wednesday, March 16, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">Friday, March 18, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">Monday, March 21, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">Tuesday, March 22, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">Wednesday, March 23, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">Thursday, March 24, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">Friday, March 25, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">Monday, March 28, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">Tuesday, March 29, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">Wednesday, March 30, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">Thursday, March 31, 2011</td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td > </td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A7</td><td >=IF($A$1="","",<br />IF<span style=' color:008000; '>(MONTH<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(WORKDAY<span style=' color:#804000; '>(DATEVALUE<span style=' color:#ff7837; '>($A$1&" 1"&", "&YEAR<span style=' color:#8000ff; '>(TODAY<span style=' color:#545fa5; '>()</span>)</span>)</span>,ROW<span style=' color:#ff7837; '>(A1)</span>-1,$C$2:$C$20)</span>)</span>)</span><>MONTH<span style=' color:#0000ff; '>(DATEVALUE<span style=' color:#ff0000; '>($A$1&" 1, 2011")</span>)</span>,"",WORKDAY<span style=' color:#0000ff; '>(DATEVALUE<span style=' color:#ff0000; '>($A$1&" 1"&", "&YEAR<span style=' color:#804000; '>(TODAY<span style=' color:#ff7837; '>()</span>)</span>)</span>,ROW<span style=' color:#ff0000; '>(A1)</span>-1,$C$2:$C$20)</span>)</span>)</td></tr></table></td></tr></table>

 

[msk7777]

Thanks! Going to try this! In formula 2 is the cells listed in column M refering to my Holiday list?

With
A1: containing a month name (from a dropdown list)...eg JUN

This regular formula begins the list of workdays:

B1: =WORKDAY(($A$1&"-1")-1,1,HolidayList)

And this regular formula, copied down through B31, continues the list:

EDITED....B2 formula replaced with a better one:
B2: =IF(B1="","",IF(TEXT(WORKDAY(B1,1,$M$2:$M$15),"mmm")<>$A$1,"",WORKDAY(B1,1,$M$2:$M$15)))
<?XML:NAMESPACE PREFIX = NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2 /><NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>

</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Format those cells as dates.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p></NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Note_1: HolidayList refers to whatever range you used to identify holidays.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>Note_2: the space before and afte the < symbol are there to prevent this website from interpreting the symbol as an HTML tag.</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p></NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>
<NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")< p>
Is that something you can work with?
</NETWORKDAYS($A$1&"-1",EOMONTH($A$1&"-1",0),$M$2:$M$15),WORKDAY(B1,1,HolidayList),"")<>

 

[Misca]

Here's my try:

Excel Workbook
	A	B	C
1	tammikuu
2			Holidays:
3			1.1.11
4			6.1.11
5
6	Workdays:		Data Validation List:
7	3.1.2011		tammikuu
8	3.1.2011		helmikuu
9	4.1.2011		maaliskuu
10	5.1.2011		huhtikuu
11	7.1.2011		toukokuu
12	10.1.2011		keskuu
13	11.1.2011		heinkuu
14	12.1.2011		elokuu
15	13.1.2011		syyskuu
16	14.1.2011		lokakuu
17	17.1.2011		marraskuu
18	18.1.2011		joulukuu
Sheet

All the month names on the Data Validation List are real dates (1.1.2011 for January etc.) but the cell format is "MMM". This way the drop down list in A1 displays the dates as month names as well. A1 should be formatted "MMM" as well.

My Excel uses the Finnish language so the month names are displayed in Finnish.

 

[Ron Coderre]

Thanks! Going to try this! In formula 2 is the cells listed in column M refering to my Holiday list?

OOPS! My mistake...When I pasted in the new formula, I forgot to replace the $M$2:$M$15 reference with the word: HolidayList.

Thanks for asking

 

[msk7777]

Thanks everyone! I tried them all and they work great! This helped a ton!

 

[msk7777]

I am using this formula below, but have run into one issue. When selecting "January" (For example) in the DV list it is still showing January 1st as the first net work day of the month even though 1/1/2011 is listed withing the Named Range "HolidayList". Any thoughts?

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:190px;" /><col style="width:22px;" /><col style="width:76.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td></tr><tr style="height:26px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >March</td><td >*</td><td style="font-weight:bold; ">Holidays</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >*</td><td >*</td><td style="text-align:right; ">1/1/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >*</td><td >*</td><td style="text-align:right; ">3/9/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td style="text-align:right; ">3/17/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >*</td><td >*</td><td style="text-align:right; ">4/22/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >*</td><td style="text-align:right; ">5/30/2011</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">Tuesday, March 01, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">Wednesday, March 02, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">Thursday, March 03, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">Friday, March 04, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">Monday, March 07, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">Tuesday, March 08, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">Thursday, March 10, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">Friday, March 11, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">Monday, March 14, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">Tuesday, March 15, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">Wednesday, March 16, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">Friday, March 18, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">Monday, March 21, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">Tuesday, March 22, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">Wednesday, March 23, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">Thursday, March 24, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">Friday, March 25, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">Monday, March 28, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">Tuesday, March 29, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">Wednesday, March 30, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">Thursday, March 31, 2011</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >*</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A7</td><td >=IF($A$1="","",<br />IF<span style=' color:008000; '>(MONTH<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(WORKDAY<span style=' color:#804000; '>(DATEVALUE<span style=' color:#ff7837; '>($A$1&" 1"&", "&YEAR<span style=' color:#8000ff; '>(TODAY<span style=' color:#545fa5; '>()</span>)</span>)</span>,ROW<span style=' color:#ff7837; '>(A1)</span>-1,$C$2:$C$20)</span>)</span>)</span><>MONTH<span style=' color:#0000ff; '>(DATEVALUE<span style=' color:#ff0000; '>($A$1&" 1, 2011")</span>)</span>,"",WORKDAY<span style=' color:#0000ff; '>(DATEVALUE<span style=' color:#ff0000; '>($A$1&" 1"&", "&YEAR<span style=' color:#804000; '>(TODAY<span style=' color:#ff7837; '>()</span>)</span>)</span>,ROW<span style=' color:#ff0000; '>(A1)</span>-1,$C$2:$C$20)</span>)</span>)</td></tr></table></td></tr></table>

 

[AlphaFrog]

Sorry, try this...

=IF($A$1="","",
IF(MONTH((WORKDAY(DATEVALUE($A$1&" 1"&", "&YEAR(TODAY()))-1,ROW(A5),$C$2:$C$20)))<>MONTH(DATEVALUE($A$1&" 1, 2011")),"",
WORKDAY(DATEVALUE($A$1&" 1"&", "&YEAR(TODAY()))-1,ROW(A5),$C$2:$C$20)))

Or a little shorter version of the same thing...

=IF($A$1="","",
IF(MONTH((WORKDAY(DATEVALUE($A$1&" 1")-1,ROW(A1),$C$2:$C$20)))<>MONTH(DATEVALUE($A$1&" 1")),"",
WORKDAY(DATEVALUE($A$1&" 1")-1,ROW(A1),$C$2:$C$20)))

 

[barry houdini]

Try this version of Ron's approach....in A7 copied down

=IF(TEXT(WORKDAY((1&A$1)-1,ROWS(A$7:A7),C$2:C$20),"mmmm")<>A$1,"",WORKDAY((1&A$1)-1,ROWS(A$7:A7),C$2:C$20))