Automatically populate dates in specific month without going into new month

[JCTalk]

Hi Guys,

I'm building a spreadsheet that I want to reuse for various areas of my business. What I'm trying to do is use a formula to populate all the dates in the month, so I can use data validation to switch between months. Data is irrelevant, formulas will pick that up from other tabs.

I have a starting month selector, which allows me to get the 1st of the month from it, but the tricky bit is populating all the dates in the month, without going into a new month.

My formula to get each month is simple I guess of: =A1+1 (copied down) but when it gets down to the end of the month it populates dates into the next month. I want it to stop at the end of the specified month and any cell after that bring back a blank cell.

Eg.

01/05/2015
02/05/2015
03/05/2015
...
31/05/2015
01/06/2015 <<< This is where it carries on. If the month selected is May, I only want it populate dates in May. The rest of the cells with formulas in should return a blank cell.

Any thoughts? I assume some kind of IF statement, but when I try to say IF month > the month selected it doesn't work. I know what I'm trying to do, I just can't stumble into the formula lol.

 

[eibi]

JC,

Here's my suggestion...I hard coded the date in cell A1, and then put the formula in A2 and dragged down as far as a wanted.

=IFERROR(IF(A1+1<=EOMONTH(A1,0),A1+1,""),"")

See attached.

Is this what you are wanting?

 

[eibi]

Here it is in the sample file you provided...

 

[eibi]

I'm kind of interested in avoiding the IFERROR when possible...Here's another, without using the IFERROR.

=IF(ROWS($4:4)<(EOMONTH($A$1,0)-$A$1+1),A3+1,"")

Paste in Cell A4 of your table and drag down...

 

[JCTalk]

Hi Eibi,

Many thanks for your super quick reply. That's fantastic. Just what I was hoping for. Makes my workbook look far more functional and visually pleasing when coupled with a bit of conditional formatting.

Many thanks again Eibi.