DATEDIF issue where all columns are formatting the same?

[3G]

HI there-

I have 5 rows of data, all using a DATEDIF formula. For some reason, there is only 1 row that throws a #NUM! error. I have double checked all formatting, and, in the formula arguements window there are no errors reported (granted it says Volatile), but, it's identical to the ones that work. I'm stumped! Here's my formula:


=DATEDIF(Sheet1!$N$2,TODAY(),"d")


where N2 is 9/1/2011. It's a custom date format of mmm-yyy, but, ALL of the others have the same formula, using different columns. For some reason, ONLY this one throws an error. The error check in the formula shows this:


DATEDIF(40787,40765,"d".

The next evaluation will result in an error. There is a problem with a number in this formula. (*note, the 40765 is italicized)


Thanks

 

[Luke M]

DATEDIF requires that the first arguement be the earlier date. 9/1/2011 is after today, hence the error.


Perhaps a more robust formula would work?

=DATEDIF(MIN(TODAY(),Sheet1!$N$2),MAX(TODAY(),Sheet1!$N$2),"d")

 

[3G]

Luke...you Ninja you! Thanks. It didn't even dawn on me. Ha!


Thanks

3G

 

[Luke M]

Aw, you're very welcome 3G.