When I enter =datevalue(a1), where A1 = 16/07/2016, I get an err:502.
Windows 10 Home, Toshiba Satellite.
Any ideas?
Thanks.
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.
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.
It’s March 2022, and I’m getting the same Err:502 error code, but only for dates in May? I’m using version 7.2.5.2, but it’s really weird that the formula works for all of the other date text cells that have the format "mmm. d, yyyy". Is this a bug because May is the only three letter month and the rest are abbreviated? Maybe it is because the database I don’t have control over exports May with a . after it even though it’s not an abbreviation?
DATEVALUE("May. 4, 2011") gives Err:502 under en-US locale; but DATEVALUE("May 4, 2011") works (note the absent dot). No idea if that is correct (using dot likely implies abbreviation - but May is not abbreviated?)
1 Like
Select the column of dates.
Call the cell format dialog, tab “Numbers” and set the language to “English (USA)”.
menu:Edit>Find&Replace…
Search: .
Replace: Nothing
Current Selection: ON
Regular expressions= OFF
[Replace All]
Now you should have correct date values right aligned to the cell borders. Apply any number format in any langauge you like. Once you have correct numeric values, the cell format makes no difference anymore.
1 Like
That works with one change. My Find & Replace had Entire cells selected by default, which resulted in a Search key not found error. Deselecting that fixed the issue, converted that column date values, and displayed them with the default date format. Thanks for that idea because it was very close!
Indeed, I missed that option. Thank you for putting this straight. The important thing here is that the remaining string without the point is interpreted in US American context with English month names. before day number.
OK, this one works with UK English as well but US English or rest-of-the-world English becomes important when you 3 numbers as in 1/2/1999 where the program can not know if the text means 1st of February or 2nd of January.
1 Like
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.
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”).
I think in Russian ‘.’ dot it’s the date’s separator.
I have explicitly mentioned the accepted patterns 
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: 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?
Nah, I consider it a bug as well.