DateValue formula

[Klemen25]

Hello

If I select A1 and format cell to date and input 1.1.2009 and than in
B1 input formula =datevalue(A1) i get #value error.

If I set the A1 cell format to custom and format d.m.yyyy and than in
B1 input the same formula i get again #value error.

If I write formula =datevalue("1.1.2009") it returns 39814 nicely.

Why the first two cases return #value?

 

[Roger Govier]

Hi

DateValue turns Text into a date serial number.
A cell that has been formatted as Date, is a Number as Excel holds dates
internally as a serial number.
That is why you get the #Value error.

If you had entered "1.1.2009" into cell A1, then it would have returned you
the 39814 value.

 

[Dave Peterson]

I use xl2003 and get an error for both values.

Hello

If I select A1 and format cell to date and input 1.1.2009 and than in
B1 input formula =datevalue(A1) i get #value error.

If I set the A1 cell format to custom and format d.m.yyyy and than in
B1 input the same formula i get again #value error.

If I write formula =datevalue("1.1.2009") it returns 39814 nicely.

Why the first two cases return #value?

 

[Klemen25]

Great! Thank you!

Just for follow up if someone else from Europe reads this:
"1.1.2009" didn't work in my excel (also 2003).
'1.1.2009 worked fine and it returned the correct number!

Thank you again!