software

Hello,

I have a basic formula in J6 =EDATE(I6;1)

I'm try to extend this formula from J6 to the right (every cell adds one
month to the previous), but once the EDATE([...]6;1) will get bigger than
the value in cell D4, I would like to have the formula giving me quot;quot; (empty
cell).

I started with
=IF(EDATE(I4;1)gt;$D$4;quot;quot;;EDATE(I4;1))
but it's not good enough: the first cell that is bigger than $D$4 is indeed
blank, but all the following give me #VALUE error.

So I modified to
=IF(OR(EDATE(I4;1)gt;$D$4;ISERROR(EDATE(I4;1)));quot;quot;;E DATE(I4;1))
but it's the same: first cell bigger than D4 value is blank (that's fine!),
but then I still can't get rid of #VALUE errors that follow (:-().

So I reformulated it to
=IF(ISERROR(EDATE(I4;1));quot;quot;;IF(EDATE(I4;1)gt;$D$4;quot;quot; ;EDATE(I4;1)))
and it's functionning correctly (finally!): all the cells bigger than the
value in D4 are blank!

I'm asking however myself if I can simplify the last formula and make it
look quot;nicerquot;...
The other question I have is why the second formula (with OR argument) is
not functionning like the third...

Thank you in advance for any hints regarding this,
Regards,

Mark

if(edate(i4;1)lt;=$d$4,edate(i4;1);quot;quot;)

or use the column function

if(edate($i4;column()-9)lt;=$d$4,edate($i4;(column()-9);quot;quot;)

J is the 10th column hence column-9=1 and will increase as the formula
is copied to the right

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=542420Perhaps this

=IF(I4=quot;quot;,quot;quot;,IF(EDATE(I4,1)gt;$D$6,quot;quot;,EDATE(I4,1)))

The problem is that OR gets evaluated even if the first condition fails, so
you get the #VALUE

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;markxquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I have a basic formula in J6 =EDATE(I6;1)
gt;
gt; I'm try to extend this formula from J6 to the right (every cell adds one
gt; month to the previous), but once the EDATE([...]6;1) will get bigger than
gt; the value in cell D4, I would like to have the formula giving me quot;quot; (empty
gt; cell).
gt;
gt; I started with
gt; =IF(EDATE(I4;1)gt;$D$4;quot;quot;;EDATE(I4;1))
gt; but it's not good enough: the first cell that is bigger than $D$4 is
indeed
gt; blank, but all the following give me #VALUE error.
gt;
gt; So I modified to
gt; =IF(OR(EDATE(I4;1)gt;$D$4;ISERROR(EDATE(I4;1)));quot;quot;;E DATE(I4;1))
gt; but it's the same: first cell bigger than D4 value is blank (that's
fine!),
gt; but then I still can't get rid of #VALUE errors that follow (:-().
gt;
gt; So I reformulated it to
gt; =IF(ISERROR(EDATE(I4;1));quot;quot;;IF(EDATE(I4;1)gt;$D$4;quot;quot; ;EDATE(I4;1)))
gt; and it's functionning correctly (finally!): all the cells bigger than the
gt; value in D4 are blank!
gt;
gt; I'm asking however myself if I can simplify the last formula and make it
gt; look quot;nicerquot;...
gt; The other question I have is why the second formula (with OR argument) is
gt; not functionning like the third...
gt;
gt; Thank you in advance for any hints regarding this,
gt; Regards,
gt;
gt; Mark
gt;
gt;
On Tue, 16 May 2006 11:53:48 0200, quot;markxquot; gt;
wrote:

gt;Hello,
gt;
gt;I have a basic formula in J6 =EDATE(I6;1)
gt;
gt;I'm try to extend this formula from J6 to the right (every cell adds one
gt;month to the previous), but once the EDATE([...]6;1) will get bigger than
gt;the value in cell D4, I would like to have the formula giving me quot;quot; (empty
gt;cell).
gt;
gt;I started with
gt;=IF(EDATE(I4;1)gt;$D$4;quot;quot;;EDATE(I4;1))
gt;but it's not good enough: the first cell that is bigger than $D$4 is indeed
gt;blank, but all the following give me #VALUE error.
gt;
gt;So I modified to
gt;=IF(OR(EDATE(I4;1)gt;$D$4;ISERROR(EDATE(I4;1)));quot;quot;; EDATE(I4;1))
gt;but it's the same: first cell bigger than D4 value is blank (that's fine!),
gt;but then I still can't get rid of #VALUE errors that follow (:-().
gt;
gt;So I reformulated it to
gt;=IF(ISERROR(EDATE(I4;1));quot;quot;;IF(EDATE(I4;1)gt;$D$4;quot; quot;;EDATE(I4;1)))
gt;and it's functionning correctly (finally!): all the cells bigger than the
gt;value in D4 are blank!
gt;
gt;I'm asking however myself if I can simplify the last formula and make it
gt;look quot;nicerquot;...
gt;The other question I have is why the second formula (with OR argument) is
gt;not functionning like the third...
gt;
gt;Thank you in advance for any hints regarding this,
gt;Regards,
gt;
gt;Mark
gt;

The quot;trickquot;, if you will, is to reference everything back to the original date.
Since you've changed references from Row 4 to Row 6, I'm not certain exactly
what you want, but something like this, will work. Adjust to fit your cell
references:

D6:Maximum Date
I6:Base Date
J6:=IF(edate($I$6,COLUMNS($A:A))gt;$D$6,quot;quot;,edate($I$6,C OLUMNS($A:A)))

Copy/Drag across as far as you wish.

The COLUMNS($A:A) function acts as a counter and will increase by one with each
cell dragged across.

In addition, by referring back to Base Date and using a increment, you avoid
the problem of unwanted date changes if base date is, for example, 31 Jan 2006.

--ron

I think the problem is that once you have set a cell to blank then
EDATE( ... ) will return an error if it acts upon that cell.

Try this (a variation of your second formula):

=IF(I4=quot;quot;;quot;quot;;IF(EDATE(I4;1)gt;$D$4;quot;quot;;EDATE(I4;1)))

and then copy this across.

Hope this helps.

Pete