plus 6 months

  • Thread starter Thread starter lesiofamily
  • Start date Start date
L

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
 
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.
 
Clarification:
EDATE requires the Analysis ToolPak add-in be installed
Click to expand...

For Excel versions prior to Excel 2007.

--
Biff
Microsoft Excel MVP


T. Valko said:
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.
Click to expand...
 
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.
 
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.
 
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.
 
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


Rick Rothstein said:
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.
Click to expand...
 
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


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

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
 
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
 
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.
 
thanks a lot!

--
lb
T. Valko said:
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.
Click to expand...
 
Back
Top