Adding months to date at end of month
 

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

Adding months to date at end of month
 

"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.

Adding months to date at end of 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

Adding months to date at end of month
 

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

Adding months to date at end of 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.