Formula to add a date to a cell that is 6 months from today

[JEFFWI]

Greetings,

I need to write a formula that is 6 months past any given date. I am not
sure what the correct terminology is but in terms of being 6 months out, it
needs to be 6 calendar months. Here is my current formula:

=DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))

When the day part of the date is 29, 30 or 31 _and_ 6 months from that date
doesn't contain a day that is 29, 30 or 31 my formula rolls over to the
following month. For example if my date in A1 is 8/29/2008 the result of my
formula is 3/1/2009. What I really want is 2/28/2008, which is the last day
of the 6th month from 3/1.

What is the best way to special case dates where the day value is 29, 30 or
31.

 

[Mike H]

I'm sure someone is going to come up with something much simpler but until
then:-

=IF(DAY(A1)>=29,EOMONTH(A1,6),DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)))

With your date in a1

Mike

 

[Mike H]

Forgot to mention, if you get the VALUE error then

Tools|Addins and check the analysis toolpack

Mike

 

[Niek Otten]

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

Accounts for leap years too.

Mind the 7 in the formula; if you change the number of future months , change this accordingly.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Greetings,
|
| I need to write a formula that is 6 months past any given date. I am not
| sure what the correct terminology is but in terms of being 6 months out, it
| needs to be 6 calendar months. Here is my current formula:
|
| =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))
|
| When the day part of the date is 29, 30 or 31 _and_ 6 months from that date
| doesn't contain a day that is 29, 30 or 31 my formula rolls over to the
| following month. For example if my date in A1 is 8/29/2008 the result of my
| formula is 3/1/2009. What I really want is 2/28/2008, which is the last day
| of the 6th month from 3/1.
|
| What is the best way to special case dates where the day value is 29, 30 or
| 31.

 

[Mike H]

Hmmmmmm

=EDATE(A1,6)

Mike

 

[Niek Otten]

LOL!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hmmmmmm
|
| =EDATE(A1,6)
|
| Mike
|
| "JEFFWI" wrote:
|
| > Greetings,
| >
| > I need to write a formula that is 6 months past any given date. I am not
| > sure what the correct terminology is but in terms of being 6 months out, it
| > needs to be 6 calendar months. Here is my current formula:
| >
| > =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))
| >
| > When the day part of the date is 29, 30 or 31 _and_ 6 months from that date
| > doesn't contain a day that is 29, 30 or 31 my formula rolls over to the
| > following month. For example if my date in A1 is 8/29/2008 the result of my
| > formula is 3/1/2009. What I really want is 2/28/2008, which is the last day
| > of the 6th month from 3/1.
| >
| > What is the best way to special case dates where the day value is 29, 30 or
| > 31.

 

[JEFFWI]

Thanks guys! Works great!