plus 6 months

[lesiofamily]

is there a function which would calculate new date from a given date?
e.g. column A row 1= 07.03.2009
I would like column B row 1 to show= 01.03.2010 - which is in 6 months
is it possible? any suggestions welcome

thank you

 

[T. Valko]

Try this:

=EDATE(A1,6)

Format as Date.

EDATE requires the Analysis ToolPak add-in be installed. If you enter the
formula and get a #NAME? error see help on the EDATE function and it'll tell
how to fix the problem.

 

[T. Valko]

Clarification:

EDATE requires the Analysis ToolPak add-in be installed

For Excel versions prior to Excel 2007.

--
Biff
Microsoft Excel MVP

Try this:

=EDATE(A1,6)

Format as Date.

EDATE requires the Analysis ToolPak add-in be installed. If you enter the
formula and get a #NAME? error see help on the EDATE function and it'll
tell how to fix the problem.

 

[OssieMac]

EDATE function returns a date for a specified number of months from specified
date. Lookup in Help for more info on the function. Note can use positive or
negative months for months ahead or before specified date.

 

[Shane Devenshire]

Hi,

try

=EDATE(A1,6)

This function will return the date 6 months in the future, actually the same
day 6 months in the future.

The function is an ATP one so in 2003 you need to attach it by choosing
Tools, Add-ins and checking Analysis ToolPak.

 

[Rick Rothstein]

The only thing you have to be aware of when using EDATE is how it handles
the end of long months to short months 6 months hence. For example, EDATE
will return 2/28/2010 for start dates of 8/28/2009 through 8/31/2009
inclusive.

 

[basilio]

I believe the best function to use in your case is <DATE>
when you want to go ahead by 6 months from the date ie 25/3/2009 (eu format)
located in cell say..B1, try " =date(Year(B1);Month(B1)+6;Day(B1)).
this will get you exactly to the same month day and 6 months ahead. Same is
valid when going backwards .
--
Basilio

The only thing you have to be aware of when using EDATE is how it handles
the end of long months to short months 6 months hence. For example, EDATE
will return 2/28/2010 for start dates of 8/28/2009 through 8/31/2009
inclusive.

 

[basilio]

You may try the same with days but you must know the exact days number.
Or try "=Date(year(B1);month(B1)+6;1)" to get to the first day of the 6th
month ahead.
--
Basilio

I believe the best function to use in your case is <DATE>
when you want to go ahead by 6 months from the date ie 25/3/2009 (eu format)
located in cell say..B1, try " =date(Year(B1);Month(B1)+6;Day(B1)).
this will get you exactly to the same month day and 6 months ahead. Same is
valid when going backwards .

 

[Ron Rosenfeld]

I believe the best function to use in your case is <DATE>
when you want to go ahead by 6 months from the date ie 25/3/2009 (eu format)
located in cell say..B1, try " =date(Year(B1);Month(B1)+6;Day(B1)).
this will get you exactly to the same month day and 6 months ahead. Same is
valid when going backwards .

Not really. You still have the long month issue, just turned around
differently.

For example, using your formula,

28-Aug-2009 --> 28-Feb-2010
29-Aug-2009 --> 1-Mar-2010
30-Aug-2009 --> 2-Mar-2010

An alternative, using the DATE function, would be:

=MIN(DATE(YEAR(B1),MONTH(B1)+6+{1,0},DAY(B1)*{0,1}))

This does the same as the EDATE function:

28-Aug-2009 --> 28-Feb-2010
29-Aug-2009 --> 28-Feb-2010
30-Aug-2009 --> 28-Feb-2010

I believe most people would accept the latter rather than the former, but
whoever is using the data needs to understand that "month" is an imprecise
term.
--ron

 

[lesiofamily]

guys, you are great!
thank you very much

 

[lesiofamily]

ok, now something extra
I have 100 cells in column A (each row will have a different date at some
point, at this time some of them are still empty) and I do not want to write
the formula 100 times for column B
is there a way to write a formula which will take care of all rows in column
B regarding date + 6 months?

lb

 

[T. Valko]

Try this...

A1:A100 = dates, some empty cells

Enter this formula in B1 and copy down to B100:

=IF(A1="","",EDATE(A1,6))

Format B1:B100 as Date

Note that the EDATE function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the EDATE
function. It'll tell you how to fix the problem.

 

[lesiofamily]

thanks a lot!

--
lb

Try this...

A1:A100 = dates, some empty cells

Enter this formula in B1 and copy down to B100:

=IF(A1="","",EDATE(A1,6))

Format B1:B100 as Date

Note that the EDATE function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the EDATE
function. It'll tell you how to fix the problem.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

thanks a lot!