Excel gives #VALUE! error with DATEVALUE function.

[Guest]

Ok, I need to find the difference between two dates so I'm using the
DATEVALUE function to do this. But I keep getting a damn VALUE error.
I've even tried entering the examples that excel gives you but I still get
the same thing.
Shouldn't it be for example =DATEVALUE("01/31/2003")
Thanks for any help

 

[Niek Otten]

I won't comment on the second half of your name, but if you're really
Italian, should your date not be "31/1/2003"?
Anyway, check your dat format with your Windows settings


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi
could be caused by your Windows date settings. If you have to use a
string representation there're only a few formats which are recognised
in all Excel versions without a problem. e.g.
YYYY-MM-DD -> 2004-01-31
YYYY/MM/DD -> 1004/01/31

Most other formats could break depending on the regional settings so
don't use them. Both of the above should work in DATEVALUE but I would
recommend to use the dATE function instead. e.g.
=DATE(2003,1,31)

 

[Jyotsana]

Hi,
You have to convert the date to a text format. You can use the following syntax:
DATEVALUE(TEXT(E2,"mm/dd/yyyy"))
where E2 is the cell location of the date and mm/dd/yyyy is the format specified