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