Adding months to date at end of month

  • Thread starter Thread starter Shaggyjh
  • Start date Start date
S

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
 
Shaggyjh said:
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
Click to expand...

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.
 
Unless I've completely misunderstood your requirement, try this:-

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

Mike
 
Thanks alot Stephen that works a treat.



Stephen said:
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.
Click to expand...
 
Back
Top