Adding months to date at end of month

[Shaggyjh]

Hi,

I have searched but can't see this specific question.

Basically the question is how to add moths to a date, i need to have a
column that has column F's dates + 10 months. However, the column F dates
are all month end days, so 31/8/07 or 28/02/06 or 30/06/07.

I have tried the edate function and also tried date(year(f),month(f)+10,
day(f)) formula, but both gives 28/12/07 if the figure in the original column
was February.

Is there a way of rounding the date up or down to the last day of the month?

Many thanks

 

[Stephen]

Hi,

I have searched but can't see this specific question.

Basically the question is how to add moths to a date, i need to have a
column that has column F's dates + 10 months. However, the column F dates
are all month end days, so 31/8/07 or 28/02/06 or 30/06/07.

I have tried the edate function and also tried date(year(f),month(f)+10,
day(f)) formula, but both gives 28/12/07 if the figure in the original
column
was February.

Is there a way of rounding the date up or down to the last day of the
month?

Many thanks

Try this:
=DATE(YEAR(A1),MONTH(A1)+11,0)
It adds 11 months to your date, but then gives the "0th" of that month, that
is the day before the 1st, or the last day of the previous month.

 

[Mike H]

Unless I've completely misunderstood your requirement, try this:-

=DATE(YEAR(F1),MONTH(F1)+10,DAY(F1))

Mike

 

[Mike H]

Yes I may well have misunderstood, try this instead

=EOMONTH(F1,10)

Mike

 

[Shaggyjh]

Thanks alot Stephen that works a treat.

Try this:
=DATE(YEAR(A1),MONTH(A1)+11,0)
It adds 11 months to your date, but then gives the "0th" of that month, that
is the day before the 1st, or the last day of the previous month.