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

  • Thread starter Thread starter JEFFWI
  • Start date Start date
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.
 
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
 
Forgot to mention, if you get the VALUE error then

Tools|Addins and check the analysis toolpack

Mike
 
=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.
 
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

Conditional formula 4
Formula to calculate exact months and days between two dates 3
Month Deduct Formula needed 2
I am trying to create a formula 2
match one column with dates to several colums with dates, copy pas 4
Identify months/periods applicable to Date range 2
Add years, months, days to a date 5
count dates 1

Back
Top