Ask Your Question

datevalue returns err:502 SOLVED

asked 2018-11-28 13:19:03 +0200

sebrof gravatar image

updated 2018-11-28 13:46:49 +0200

When I enter =datevalue(a1), where A1 = 16/07/2016, I get an err:502.

Windows 10 Home, Toshiba Satellite.

Any ideas?


edit retag flag offensive close merge delete


Look here (36089/why-do-i-get-error-message-502-when-using-intrate-function/). If you enter in the search mask "Error 502" you will find more hints. It seems to be a formatting error.

ebot gravatar imageebot ( 2018-11-28 13:25:43 +0200 )edit

Thanks for your reply. However, I have tried numbervalue, and it works, so the problem is effectively solved. Libreoffice treats this formula (datevalue) differently from the way Excel treats it.

sebrof gravatar imagesebrof ( 2018-11-28 13:34:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-11-28 21:07:55 +0200

erAck gravatar image

updated 2018-11-28 21:10:39 +0200

If you have a text string of 16/07/2016 it depends on the locale's date order whether it can be parsed or not. i.e. this date order DMY can not be converted in an English-US locale where the date order is MDY and there is no month 16.

If your 16/07/2016 is actually a numeric date value formatted as DD/MM/YYYY date, then passing it to DATEVALUE() yields an error because DATEVALUE() expects a text string. Excel might handle that differently and simply return the underlying date serial number instead, does it? In this case NUMBERVALUE() accidentally works because it converts the underlying date serial number to text before converting it to a number again. But in this case you can also simply use =A1 and remove the date number format to have the date serial number displayed.

edit flag offensive delete link more


Well - I confirm that under Russian locale (D/M/Y) + English UI, and with the D/M/Y in date acceptance patterns, I get the error for DATEVALUE("16/07/2016").

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-28 21:50:58 +0200 )edit

I think in Russian '.' dot it's the date's separator.

m.a.riosv gravatar imagem.a.riosv ( 2018-11-28 22:12:39 +0200 )edit

I have explicitly mentioned the accepted patterns ;-)

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-28 22:23:56 +0200 )edit

Hum.. indeed, also in cell input, though the D/M/ (ru-RU default extra pattern, note the trailing slash) abbreviated input does work. If that is replaced by D/M/Y that also works, if the order is D/M/Y;D/M/ it works as well. Seems the more complete form has to come first.

erAck gravatar imageerAck ( 2018-11-30 15:08:29 +0200 )edit

@erAck: thanks; I'd consider that a bug (it's not realistic to demand users to properly order the patterns, even if we notice that in help), what do you think?

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-30 15:18:27 +0200 )edit

Nah, I consider it a bug as well.

erAck gravatar imageerAck ( 2018-11-30 15:23:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-28 13:19:03 +0200

Seen: 277 times

Last updated: Nov 28 '18