calculate 6 months forward from a date

  • Thread starter Thread starter Eclaires
  • Start date Start date
E

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
 
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
 
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.
 
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........
 
Jim Thomlinson said:
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.
Click to expand...

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 -----
 
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.
 
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.
 
Back
Top