#VALUE Error

[Windchaser]

This problem is driving me nuts! The following Excel formula works fine in
several workbooks, but in one particular worksheet it generates the # VALUE
! error. Of course this is the one I really want it to work in. Any
suggestions of what I could be doing wrong?

=DATEDIF(15487,TODAY(),"y")&" Yrs, "&DATEDIF(15487,TODAY(),"ym")&" Mnths,
"&DATEDIF(15487,TODAY(),"md")&" Dys"

Windows XP Service Pack 1, Excel 2002.

Thank you for saving my sanity.

 

[Pete_UK]

I can't see anything wrong with the formula, and have copied it to
XL2000 and it works fine. There must be something about that
particular sheet - have you tried looking through Tools | Options?

Anyway, if you want to know the answer it is:

65 Yrs, 5 Mnths, 27 Dys

Hope this helps.

Pete

 

[Windchaser]

I can't see anything wrong with the formula, and have copied it to
XL2000 and it works fine. There must be something about that
particular sheet - have you tried looking through Tools | Options?

Anyway, if you want to know the answer it is:

65 Yrs, 5 Mnths, 27 Dys

Hope this helps.

Pete

Thanks Pete, the answer is my age (cat's outa the bag now!). That's the
problem, any other workbook or worksheet it works fine, but the one
particular one I want it to work in, it gives me the # VALUE ! error,
nowhere else do I have any problems. Exact same problem with other similiar
formulas with datedif work in every other location but the one I want it to
work in.

What, in this particular worksheet could cause this error, and how do
I find it? The spreadsheet is basically a homegrown debit/asset sheet for my
checkbook tracking, nothing special in it that I am aware of.

Glen

 

[Pete_UK]

Glen,

the only way I have been able to generate the #VALUE error with that
formula is if I change one of the numbers to 15:487 or 154:87, i.e.
put a colon in it. I thought you would have copy/pasted the formula
directly into your post, but if you typed it in you may have typed it
correctly and the formula in the worksheet is incorrect - just have a
very close look at your formula in the formula bar to see if there is
anything wrong with the numbers. You might like to change them to the
actual dates.

Hope this helps.

Pete

 

[Gord Dibben]

I also tried it out in Excel 2003

No problem.

I can't replicate the error.

Also tried with the number 15549 and returned 65 Yrs, 3 Mnths, 26 Dys after
editing the formula to insert the spaces.

Another cat out of the bag<g>


Gord Dibben MS Excel MVP