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

--
lb

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.

--
Biff
Microsoft Excel MVP


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

--
lb

Clarification:

EDATE requires the Analysis ToolPak add-in be installed

For Excel versions prior to Excel 2007.

--
Biff
Microsoft Excel MVP


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

--
Biff
Microsoft Excel MVP


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

--
lb

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.


--
Regards,

OssieMac


"lesiofamily" wrote:

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

--
lb

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"lesiofamily" wrote:

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

--
lb

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.

--
Rick (MVP - Excel)


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

--
lb

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

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.

--
Rick (MVP - Excel)


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

--
lb

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

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

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.

--
Rick (MVP - Excel)


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

--
lb

On Fri, 3 Jul 2009 02:50:01 -0700, basilio
wrote:

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

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

guys, you are great!
thank you very much

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

--
lb

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

--
lb
"Ron Rosenfeld" wrote in message
...
On Fri, 3 Jul 2009 02:50:01 -0700, basilio

wrote:

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

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

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.

--
Biff
Microsoft Excel MVP


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

--
lb
"Ron Rosenfeld" wrote in message
...
On Fri, 3 Jul 2009 02:50:01 -0700, basilio

wrote:

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

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

thanks a lot!

--
lb
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


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

--
lb
"Ron Rosenfeld" wrote in message
...
On Fri, 3 Jul 2009 02:50:01 -0700, basilio

wrote:

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

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

You're welcome!

--
Biff
Microsoft Excel MVP


"lesiofamily" wrote in message
...
thanks a lot!

--
lb
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


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

--
lb
"Ron Rosenfeld" wrote in message
...
On Fri, 3 Jul 2009 02:50:01 -0700, basilio

wrote:

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

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