date + 6 months

[krislyn]

I have possibly 2 dates to consider in the formula, in 2 cells. If on
cell is blank, it must calculate the other. The end result of eithe
cell should be the date + 6 months.
I tried =I
SUM(C11)=0,DATE(YEAR(B11),MONTH(B11),DAY(B11)+180),DATE(YEAR(C11),MONTH(C11),DAY(C11)+180))
It works some times but it counts days and may end up a month ahea
or behind depending on the days of the month. I want it to go forward
months and display the month and year only.
Hope this is descriptive enough.
Great site! Thanks

 

[Fred Smith]

First, "Sum(C11)=0" is the same as "C11=0"

Next, just add 6 to the month, rather than 180 to the days:

=date(year(c11),month(c11)+6,day(c11))

DATE is smart enough to wrap the year if required.

 

[Norman Harker]

Hi Krislyn!

To add 6 months to a date and to cover the possibility that the target
month may have less days than the base date use:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+7,0))))

So for your formula:

=IF(C11=0,DATE(YEAR(B11),MONTH(B11)+6,MIN(DAY($B$11),DAY(DATE(YEAR(B11),MONTH(B11)+7,0)))),=DATE(YEAR(C11),MONTH(C11)+6,MIN(DAY($C$11),DAY(DATE(YEAR(C11),MONTH(C11)+7,0)))))

The formula is more complex than just adding 6 months because it can
cope with (eg) C11 as 31-Mar-2004 or B11 as 31-Aug-2004. In those
cases it returns 30-Sep-2004 and 28-Feb-2005 respectively. You need to
satisfy yourself that these are the returns you want in those cases.

 

[Paul B]

Krislyn, and to show only the month and year format the cell as custom m/yy
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

First, "Sum(C11)=0" is the same as "C11=0"

Next, just add 6 to the month, rather than 180 to the days:

=date(year(c11),month(c11)+6,day(c11))

DATE is smart enough to wrap the year if required.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

I have possibly 2 dates to consider in the formula, in 2 cells. If one
cell is blank, it must calculate the other. The end result of either
cell should be the date + 6 months.
I tried =IF

SUM(C11)=0,DATE(YEAR(B11),MONTH(B11),DAY(B11)+180),DATE(YEAR(C11),MONTH(C11)

 

[krislyn]

Thanks so much!! I knew it was simple, but couldnt firgure the correc
way to put it in the formula

 

[Earl Kiosterud]

Fred,

There's a little potential snag here. If your date falls past the end of
the 6th month, it "wraps" into the following month, and gives the wrong
month. Try it with Mar 31-- it tries to go to Sep 30, which winds up as Oct
1. Same with Aug 30.

Try this mod:
=DATE(YEAR(C11),MONTH(C11)+6,1)

It gives the first of the month, but since you're looking only for the month
and year, it works. Format (Format - Cells - Number - Date or Custom) for
month and year only.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

First, "Sum(C11)=0" is the same as "C11=0"

Next, just add 6 to the month, rather than 180 to the days:

=date(year(c11),month(c11)+6,day(c11))

DATE is smart enough to wrap the year if required.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

I have possibly 2 dates to consider in the formula, in 2 cells. If one
cell is blank, it must calculate the other. The end result of either
cell should be the date + 6 months.
I tried =IF

SUM(C11)=0,DATE(YEAR(B11),MONTH(B11),DAY(B11)+180),DATE(YEAR(C11),MONTH(C11)

 

[macropod]

Unless you're happy for the 6-month offset to flow over into the 7th month,
you need to deal with the 'end' month sometimes having less days than the
'start' month (eg 31 August + 6 months = ?). To deal with such scenarios,
you need to use something like:

=MIN(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+7,0))

where the source date is in A1.

Cheers