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

Thanks alot Stephen that works a treat.



"Stephen" wrote:

"Shaggyjh" wrote in message
...
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.

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

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

Mike

"Shaggyjh" wrote:

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

Yes I may well have misunderstood, try this instead

=EOMONTH(F1,10)

Mike

"Shaggyjh" wrote:

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