Month End Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony - 23 Mar 2006 00:33 GMT

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.

Reply to this Message

Gordon Rainsford - 24 Mar 2006 13:32 GMT

> 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.
[quoted text clipped - 4 lines]
> 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.

Start with two helper cells that return the current month and the
current year.

eg B1 = MONTH(A1)
B2 = YEAR(A1)

Then
A2 = DATE(B2,B1,1)-1 will return the end of last month
A3 = DATE(B2,B1+2,1)-1 will return the end of next month

Signature

Gordon Rainsford

London UK

Reply to this Message

Mac Forum / Applications / Excel / March 2006

Month End Calculation