Prev: Custom Formating Letters to Numbers while Displaying Letters
Next: Frequency Counting
From: Ant on 30 Mar 2010 22:25
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

From: T. Valko on 30 Mar 2010 22:52
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.

--
Biff
Microsoft Excel MVP


"Ant" <Ant(a)discussions.microsoft.com> wrote in message
news:AAF1F827-1CDB-4865-9D49-6A23AD458915(a)microsoft.com...
> 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
>


 | 
Pages: 1
Prev: Custom Formating Letters to Numbers while Displaying Letters
Next: Frequency Counting