Calculate month-end date from date in adjacent cell?
 

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

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.



In article ,
"Matt D Francis" wrote:

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

use:

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

- Mangesh



"Matt D Francis" wrote in message
...
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

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

Matt

"JE McGimpsey" wrote:

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.



In article ,
"Matt D Francis" wrote:

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

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


"Matt D Francis" wrote in message
...
cheers, just found the EOMONTH function - should be OK to use, thanks for
the
speedy resonse!

Matt

"JE McGimpsey" wrote:

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.



In article ,
"Matt D Francis" wrote:

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