#NUM! in Date field calculation
 

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.

#NUM! in Date field calculation
 

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

#NUM! in Date field calculation
 

One way

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


--

Regards,

Peo Sjoblom

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

#NUM! in Date field calculation
 

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

#NUM! in Date field calculation
 

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