Subtracting Dates
 

Hi,
I have the following formula to subtract the dates from two different
columns:
=IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m "))

What i want the formula to do is to calculate the differnce between the two
dates. However, if there is nothing entered into the columns, then i want the
column that's calculating the difference to remain blank. And this is working
great. Until i enter in a date into Z11 but not into AD11. Then it gives me
the "=NUM!" error instead of remaining blank.

Subtracting Dates
 

Adding another IF statement to check if AD11 = "" will keep your "=NUM" from
appearing.....
=IF(AD11="","",If(DATEDIF(Z11,AD11,"m")=0,"",DATED IF(Z11,AD11,"m")))

"Shannan" wrote:

Hi,
I have the following formula to subtract the dates from two different
columns:
=IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m "))

What i want the formula to do is to calculate the differnce between the two
dates. However, if there is nothing entered into the columns, then i want the
column that's calculating the difference to remain blank. And this is working
great. Until i enter in a date into Z11 but not into AD11. Then it gives me
the "=NUM!" error instead of remaining blank.

Subtracting Dates
 

Hi Shannan,

You could use:
=IF(OR(Z11="",AD11=""),0,DATEDIF(Z11,AD11,"m"))
and format to the cell with:
0;-0;
via Format|Cells|Number|Custom. This way, if either Z11 or AD11 is empty or has a 0 in it, or the months difference is zero, no
result will be displayed.

--
Cheers
macropod
[Microsoft MVP - Word]


"Shannan" wrote in message ...
Hi,
I have the following formula to subtract the dates from two different
columns:
=IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m "))

What i want the formula to do is to calculate the differnce between the two
dates. However, if there is nothing entered into the columns, then i want the
column that's calculating the difference to remain blank. And this is working
great. Until i enter in a date into Z11 but not into AD11. Then it gives me
the "=NUM!" error instead of remaining blank.