calculate 6 months forward from a date

[Eclaires]

Please can someone help, I need a formula to calculate a date 6 months
forward from another date and return the future date as the answer in a cell.
ie: start date 27/01/2010 answer will be 27/07/2010.

Any help would be much appreciated.

Thanks in advance


Viv

 

[Jim Thomlinson]

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

Where you date is in cell A1

 

[Eclaires]

Thank you for your help Jim, you are a star

Viv

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

Where you date is in cell A1

 

[Vivek]

Hi,

You can use the EDATE function as follows......
Type the date 01/27/2010 in a cell A1.
Enter the formula =EDATE(A1,6)

Vivek

 

[Jim Thomlinson]

While that function will work it requires the Analysis Toolpack addin. If
that is not installed on your computer or you send the file to someone
without the toolpack installed the function will error out.

I am not saying not to use that function but it is my preference to avoid
Analysis Toolpack functions where other regular functions can be substituted.
XL2007 does not require the toolpack to be installed as all functions are
built in without the toolpack.

 

[Vivek]

Now here is a trick.......
Enter the date 01/27/2010 in cell A2
Then enter the formula
=DATEVALUE((MONTH(A2)+6)&"/"&DAY(A2)&"/"&YEAR(A2))

Hope this works........

 

[Joe User]

I am not saying not to use that function but it is my preference to avoid
Analysis Toolpack functions where other regular functions can be
substituted.

I agree with you in principle. Moreover, in some environments, some people
do not have access to the ATP.

But in this case, making four function calls where one will do seems
wasteful.

And DATE(YEAR(A1),6+MONTH(A1),DAY(A1)) might yield the wrong result,
depending on the OP's requirements or expectations.

Consider the 31st of Mar, May, Oct and Dec and the 29th through 31st of Aug.
For most purposes, the date 6 months forward should be the 30th of Sep, Nov,
Apr and Jun and the 28th or 29th of Feb depending on the year.


----- original message -----

 

[macropod]

Hi Eclaires,

Unless you've got the Analysis Tool Pack installed, so you can use the EDATE function, you need a formula like:
=MIN(DATE(YEAR(A1),MONTH(A1)+6+{0,1},DAY(A1)*{1,0}))
You can't simply use:
=DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))
because that doesn't handle the last day of the month correctly for all months.

 

[David Biddulph]

But that won't work for a user whose Windows Regional Options are set to
dd/mm/yyyy, rather than to mm/dd/yyy.

Beter to use DATE, which is unambiguous, rather than DATEVALUE.