Calculate month-end date from date in adjacent cell?

[Guest]

Hi,

I need to be able to enter a date in a cell and have the cell next to it
return the corresponding month-end date. I'm sure it's simple but I couldn't
find a dedicated function.

e.g

A1 = 03/01/2005
A2 = 31/03/2005 (calculated)

or

A1 = 17/021/2005
A2 = 28/02/2005 (calculated)

etc

 

[JE McGimpsey]

One way:

To XL, the 0th day of the month is the last day of the previous month,
so:

A2: =DATE(YEAR(A1),MONTH(A1)+1,0)

You could also use the Analysis ToolPak Addin's EOMONTH() function:

A2: =EOMONTH(A1, 0)

but any users of the sheet will have to have the ATP installed.

 

[Mangesh]

use:

=DATE(YEAR(A1),MONTH(A1)+1,1)-1

- Mangesh

 

[Guest]

cheers, just found the EOMONTH function - should be OK to use, thanks for the
speedy resonse!

Matt

 

[Mangesh]

You need to have the Addin Analysis ToolPak enabled to use the EOMONTH and
hence that function was not advised in the first place.

- Mangesh