Populating Row with dates

[Ant]

Hello,

I am attempting to automatically fill a row with dates.

In Cell A5, I enter the first day of the month eg 1 April 2010.

I then want the adjacent columns to automatically fill with the subsequent
dates eg cell B5 is 2 April 2010, cell C5 is 3 April 2010 etc,

It must only fill upto and including the last day of the month and not roll
into May. These cells then allow my Vlookup function and graph to operate
solely for that particular month.

Obviously just adding +1 to the previous cell will do this, but then I may
end up with dates in the next month which is what I need to avoid.

Preferably the function would only work when the date in A5 is changed so I
would like to avoid using a button.

Regards
Ant

 

[T. Valko]

One way...

A5 = the first of the month date like 4/1/2010

Enter this formula in B5 and copy across to AE5:

=IF($A5="","",IF(COLUMNS($A5:B5)>DAY(EOMONTH($A5,0)),"",A5+1))

Note that the EOMONTH function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the EOMONTH
function. It'll tell you how to fix the problem.