I’m using an existing spreadsheet for our timesheet record in office. Using microsoft office works fine, but using libre will show 502 error. Attached is a sample. any idea how to fix this? Sample file
I am also seeing the #VALUE! error in the cells in MS Excel 2016 (US English UI on Russian OS); it cannot give the result of DATEVALUE(“1/Oct/2018”). Strings like that are, naturally, a nightmare, not dates. The only date format that doesn’t depend on locale settings and all peculiarities is the ISO “yyyy-mm-dd” format, which requires numeric month. Then, you’d not need the DATEVALUE in that case (slower), when you could use DATE(YYYY;MM;DD) if you had all the numbers.
Whether text can be interpreted as a date value highly depends on the locale worked in. “1/Oct/2018” is not a valid date format in en-US, where the date order is M/D/Y. Excel may be more lenient in what strings it accepts as dates, Calc is a bit more strict (though given there are two numbers with the second being 4 digits and one month name maybe could accept this as well). Working in an en-US locale any of “Oct/1/2018” or “1-Oct-2018” or “2018-Oct-1” would be accepted. Any non-English locale (where month names differ) would cease to work anyway.
To be locale (and application) independent the only safe bet is the ISO 8601 format “2018-10-01” (or “2018-10-1”) as Mike already lined out.
Thank you! all i did was to change the “/” to “-” and it worked!