Month-end dates

[klynshoe]

USING EXCEL 2010

Hello, I'm looking to fill in all the calendar month-end dates for several years. What's the best way to go about doing this (macro-free if possible).

Thanks!

 

[Jonmo1]

In any designated cell (A1 for example) put
=YEAR(TODAY())
Then whereever you want to your list of MonthEnd Dates, put
=DATE($A$1,ROWS(A$1:A2),0)
And fill down as far as desired.

Copy / paste special / values on A1 If you want the Year in A1 to be permanent.

 

[T. Valko]

USING EXCEL 2010

Hello, I'm looking to fill in all the calendar month-end dates for several years. What's the best way to go about doing this (macro-free if possible).

Thanks!

Here's another one...

Manually enter 2 or 3 of the dates:

A1 = 1/31/2012
A2 = 2/29/2012
A3 = 3/31/2012

Now, select the range A1:A3 and drag copy down as needed.

 

[Jonmo1]

Here's another one...

Manually enter 2 or 3 of the dates:

A1 = 1/31/2012
A2 = 2/29/2012
A3 = 3/31/2012

Now, select the range A1:A3 and drag copy down as needed.

Cool...That even handles leap year..

 

[hippiehacker]

There is a slightly shorter method but it can only be used when/if the Analysis Toolpak is installed. That is, Tools>Add-ins and check Analysis Toolpak. Then you you can use the EOMONTH function as shown below

=EOMONTH(A1,0)

Or,

=EOMONTH("8-10-2012",0)

 

[T. Valko]

Cool...That even handles leap year..

XL2003 doesn't appear to be smart enough to do that though..

Really?

I did it in Excel 2002.

 

[Jonmo1]

Really?

I did it in Excel 2002.

I had text dates entered in the column

Works as expected with real dates...

 

[T. Valko]

I had text dates entered in the column

Works as expected with real dates...