Subtracting Dates

  • Thread starter Thread starter Shannan
  • Start date Start date
S

Shannan

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.
 
Adding another IF statement to check if AD11 = "" will keep your "=NUM" from
appearing.....
=IF(AD11="","",If(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m")))
 
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.
 
Back
Top