Ask Your Question
0

why are the name of the days not shown in my spread sheet?

asked 2018-10-29 04:12:06 +0200

khairnut gravatar image

updated 2018-10-29 06:18:16 +0200

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

/upfiles/15407826052667099.jpg

/upfiles/15407825903713508.jpg

edit retag flag offensive close merge delete

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-29 06:39:00 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-10-29 18:38:43 +0200

erAck gravatar image

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.

edit flag offensive delete link more

Comments

Thank you! all i did was to change the "/" to "-" and it worked!

khairnut gravatar imagekhairnut ( 2018-10-30 02:01:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-29 04:12:06 +0200

Seen: 31 times

Last updated: Oct 29 '18