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 Viv,

Sure thing! You can use the DATE function in Excel to calculate a date 6 months forward from another date. Here's how:

1. First, enter the start date in a cell. Let's say it's in cell A1.
2. In another cell, enter the following formula: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
3. Press Enter, and the cell should display the future date 6 months forward from the start date.

So, in your example, if the start date is 27/01/2010 in cell A1, you would enter the formula =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) in another cell and it should return the future date of 27/07/2010.

I hope that helps!

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

Where you date is in cell A1
--
HTH...

Jim Thomlinson


"Eclaires" wrote:

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

Thank you for your help Jim, you are a star

Viv

"Jim Thomlinson" wrote:

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

Where you date is in cell A1
--
HTH...

Jim Thomlinson


"Eclaires" wrote:

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


"Eclaires" wrote:

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

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

Jim Thomlinson


"Vivek" wrote:

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


"Eclaires" wrote:

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

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" wrote:

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

Jim Thomlinson


"Vivek" wrote:

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


"Eclaires" wrote:

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" wrote:
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 -----

"Jim Thomlinson" wrote in message
...
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.
--
HTH...

Jim Thomlinson


"Vivek" wrote:

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


"Eclaires" wrote:

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

--
Cheers
macropod
[Microsoft MVP - Word]


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

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.
--
David Biddulph

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

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

Jim Thomlinson


"Vivek" wrote:

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


"Eclaires" wrote:

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