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

J

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

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
 
M

Mike H

Forgot to mention, if you get the VALUE error then

Tools|Addins and check the analysis toolpack

Mike
 
N

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display weeks from a Milestone 0
Automatically sending multiple parts of text from one word doc to another - is possible. 0
Set Up Calendar To Account For Leap Years 2
create a formula to know last months column character or number 0
I have lost 6 months work, how can i get my file back 1
Conditional formula 4
calculating Sick pay entitlement 2
Subtracting months formula 5

Top