Formula Error

[Shannan]

Hi,
I'm getting an error message (#NUM!) for the following formula and i'm not
sure why:
=IF(AD31="","",IF(DATEDIF(Z31,AD31,"m")=0,"",DATEDIF(Z31,AD31,"m")))

where AD31 has a value of 07-01-05
and Z31 has a value of 97-04-23

This is a formula that i entered into the first row and then dragged the
formula down to apply to the other cells in this column. The formula is
working in most of the other cells.

I aslo got an error message (#VALUE!) for this same formula in a different
row:
=IF(AD74="","",IF(DATEDIF(Z74,AD74,"m")=0,"",DATEDIF(Z74,AD74,"m")))

where AD74 is blank
and Z74 is 09-02-04

These two errors occur a couple of times throughout the spreadsheet, while
the rest of the cells with this formula are working fine.

 

[Jacob Skaria]

The dates may not be a valid excel date..

Format the date field to mmm-dd-yyyy to see whether it displays what you
intended.

If this post helps click Yes

 

[T. Valko]

Your formulas work ok for me when I use dates as per my regional settings:

07-01-05 = 1/5/2007 (Jan 5 2007)
97-04-23 = 4/23/1997 (Apr 23 1997)

You'll get a #NUM! error if the date in AD31 is before the date in Z31.

You'll get a #VALUE! error if any date is really a TEXT string (that may
look like a date).

 

[Shannan]

No, it can't be a formatting problem because all the other rows that have
this same formula have the dates formatted the same and they're working ok.
There's about six rows that are giving me error messages and the rest of the
3000 or so rows are working fine. :S

 

[Jacob Skaria]

Hmm.. Have you tried converting the format to mmm-dd-yyyy to see whether
those dates are displayed as what you intend.

If this post helps click Yes

 

[Shannan]

Oh! I figured it out. Someone had accidently typed "2997" as the year in the
first column instead of "1997". But thank you because i didn't know that i
could do custom formatting for dates like that, lol.

 

[Shannan]

Oh, but that was only the problem for the one that was giving me a "#NUM!"
error. The one that was giving me "VALUE!" still isn't working.