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

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.

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

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

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

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

Forgot to mention, if you get the VALUE error then

Tools|Addins and check the analysis toolpack

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.

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

=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

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

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

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.

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

LOL!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

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

Thanks guys! Works great!

"Niek Otten" wrote:

=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

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