I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month. Can anyone help, thank you so much.

if a date is in A16 then

=DATE(YEAR(A16),MONTH(A16)+1,1)-1 is the last day of the month
=DATE(YEAR(A16),MONTH(A16)+2,1)-1 is the last day of the next month
=DATE(YEAR(A16),MONTH(A16)+3,1)-1 is the last day of the month after that


--
Gary's Student


"Tony" wrote:

I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month. Can anyone help, thank you so much.

On Wed, 22 Mar 2006 16:32:27 -0800, Tony
wrote:

I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month. Can anyone help, thank you so much.

A2: =A1-DAY(A1)
A3: =A2+63-DAY(A2+63)


--ron