#NUM! in Date field calculation

[Rick]

Great site for help!
I have 3 cells.
A1 is a date cell (formatted for date mmm/yy) in which I input a start date.
A2 is a numeric value representing the length of a project in months.
A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is
formatted as a date cell.

It works to a fashion except it returns #NUM! when there is nothing in cell
A1 & A2.

How do I fix this or get around it?
I want the cell A3 to show nothing unless both A1 & A2 have inputs.

[vezerid]

Rick, it is supposed to return #NUM!. When A1 and A2 are blank your
function essentially becomes =DAY(0, 0, 0). When you input values in
these two cells, Excel will recalculate and produce a result
automatically.

If you want to avoid displaying an error value (and display, say, an
empty cell instead), use the following formula:

=IF(OR(A1=0, A2=0), "", DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1))

HTH
Kostis Vezerides

[Peo Sjoblom]

One way

=IF(COUNT(A1:A2)<2,"",(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1))))


--

Regards,

Peo Sjoblom

"Rick" <[email protected]> wrote in message
news:[email protected]...
> Great site for help!
> I have 3 cells.
> A1 is a date cell (formatted for date mmm/yy) in which I input a start
date.
> A2 is a numeric value representing the length of a project in months.
> A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is
> formatted as a date cell.
>
> It works to a fashion except it returns #NUM! when there is nothing in
cell
> A1 & A2.
>
> How do I fix this or get around it?
> I want the cell A3 to show nothing unless both A1 & A2 have inputs.

[Rick]

Thanks for the help...works great!

"vezerid" wrote:

> Rick, it is supposed to return #NUM!. When A1 and A2 are blank your
> function essentially becomes =DAY(0, 0, 0). When you input values in
> these two cells, Excel will recalculate and produce a result
> automatically.
>
> If you want to avoid displaying an error value (and display, say, an
> empty cell instead), use the following formula:
>
> =IF(OR(A1=0, A2=0), "", DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1))
>
> HTH
> Kostis Vezerides
>
>

[Rick]

Thanks for your help...it works great, also.

"Peo Sjoblom" wrote:

> One way
>
> =IF(COUNT(A1:A2)<2,"",(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1))))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Rick" <[email protected]> wrote in message
> news:[email protected]...
> > Great site for help!
> > I have 3 cells.
> > A1 is a date cell (formatted for date mmm/yy) in which I input a start
> date.
> > A2 is a numeric value representing the length of a project in months.
> > A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is
> > formatted as a date cell.
> >
> > It works to a fashion except it returns #NUM! when there is nothing in
> cell
> > A1 & A2.
> >
> > How do I fix this or get around it?
> > I want the cell A3 to show nothing unless both A1 & A2 have inputs.
>
>
>