https://bugs.documentfoundation.org/show_bug.cgi?id=166914
--- Comment #25 from Fabbian <[email protected]> --- (In reply to Eike Rathke from comment #24) > (In reply to Fabbian from comment #22) > > > OpenOffice accepts every nonsense it encounters because Excel did. > > > > It isn't nonsense and if you want interoperability you need to produce the > > same result Excel produces given the same input. > To me an input string > 1.2-3 > being accepted/interpreted as date 01/02/2003 (MM/DD/YYYY in en-US locale) > or 2001-02-03 (YYYY-MM-DD in en-CA locale) or 01.02.2003 (DD.MM.YYYY in > de-DE locale) is nonsense. Which OpenOffice does. I agree that "1.2-3" doesn't look like a reasonable date string to me either. But if Excel interprets it as one and LibreOffice doesn't you're going to break compatibility with Excel. I don't know if OpenOffice interprets it as a date string because Excel does or for some other reason. I don't have a copy of Excel to test. > (In reply to Fabbian from comment #23) > > Review https://www.iso.org/iso-8601-date-and-time-format.html and > > https://en.wikipedia.org/wiki/ISO_8601 > > > > YYYY-MM-DD complies with ISO 8601. YY-MM-DD does not. > That exact wikipedia article says it does with the year 2000 version, I > quoted before in comment 21. In any case the iso.org page is not a > reference, it only mentions one possible format, you'd have to buy the > standard from them. You omitted to mention it says that 2000 version is superseded. It further says "ISO 8601 prescribes, as a minimum, a four-digit year [YYYY] to avoid the year 2000 problem." > > Not only is the LibreOffice Calc VALUE function not compatible with Excel's > > and OpenOffice's VALUE function, it is not consistent with ISO 8601. > Anyhow, originally this was all about a YYYY/MM/DD format not being accepted > before you took > https://wiki.documentfoundation.org/Documentation/Calc_Functions/VALUE to > drop some YY-MM-DD vs YYYY-MM-DD thing. That's because my research uncovered another bug in the VALUE function. I was inspired to do that research by this earlier exchange: "(In reply to Fabbian from comment #12) > Note particularly the egregious error which interprets 6-11-25 as 11/25/2006. That's not an error, 6-11-25 is interpreted as ISO date notation, year-month-day, with two-or-less-digits year 6." Interpreting 6-11-25 as ISO date notation is incorrect. It is my opinion that the following strings should be interpreted as valid dates: xxxx-xx-xx should be recognized world-wide as a valid ISO 8601 compliant date in YYYY-MM-DD format. In a perfect world all dates would be written in that format and this discussion would be done. xxxx/xx/xx should be recognized world-wide as a valid date in YYYY/MM/DD format. It is consistent with ISO 8601 except it uses a different separator, though one which is widely recognized as a date separator. In the U. S. these rules should apply. xx-xx-xxxx should be recognized as a valid date in MM-DD-YYYY format because it is commonly used that way by many people. The four digit year makes it unambiguous. xx/xx/xxxx should be recognized as a valid date in MM/DD/YYYY format because it is commonly used that way by many people. The four digit year makes it unambiguous. xx-xx-xx should be recognized as a valid date in MM-DD-YY format because it is commonly used that way by convention. The ambiguity of the two digit year is resolved by convention. xx/xx/xx should be recognized as a valid date in MM/DD/YY format because it is commonly used that way by convention. The ambiguity of the two digit year is resolved by convention. In no case should xx/xx/xx or xx-xx-xx be interpreted as beginning with a two digit year because it is impossible to resolve the ambiguity of such a format. I don't have an opinion that applies to other parts of the world. The issue isn't whether strings should be interpreted in line with my preferences (or anyone else's). Maintaining compatibility with Excel is more important than being "correct" according to any particular convention. Studying how dates are written around the world and trying to impose some order is a fascinating problem, but beyond the scope of this discussion. I did a quick Web search to learn the conventions in Canada. Here are a few examples. From https://www.shiksha.com/studyabroad/a-complete-guide-on-date-format-articlepage-154365 Common Date Formats Across Different Countries There are mainly three date order styles, i.e. DMY, MDY, and YMD. However, knowing the date format followed across different nations in advance can help international students keep track of the application deadlines and project submission deadlines. Some of the commonly used date formats across different nations are given below. DD/MM/YYYY or DD-MM-YYYY: This format is widely used in Europe, Asia, and many other parts of the world. For example, January 27, 2025, would be represented as 27/01/2025 or 27-01-2025. MM/DD/YYYY or MM-DD-YYYY or MM DD YYYY: Predominantly used in the United States, Canada, and the Philippines. Following the same example, January 27, 2025, would be written as 01/27/2025 or 01-27-2025. YYYY/MM/DD or YYYY-MM-DD: Commonly seen in Asian countries such as Japan and China, as well as in the ISO 8601 international date format. Using this format, January 27, 2025, becomes 2025/01/27 or 2025-01-27. Month Day, Year (for eg, February 02, 2025): Frequently used in formal and literary contexts. Provides a clear and unambiguous representation of the date. Day Month Year (for eg, 02 February 2025): Long-form/full form of dates without the use of separators is often used as an easy way of representation. ISO 8601 Date Format | International Date Format ISO 8601 is the new internationally accepted standard for communicating date and time across the world. This format was launched in 1988 with its latest update in 2022. This model of date and time notations is based on the Gregorian calendar, the calendar accepted as the international standard for civil use today. The ISO 8601 date format is YYYY-MM-DD, i.e. year-month-day format. This means that today’s date in ISO 8601 format will be represented as 2025-02-02 (the second day of February in the year 2025). However, the complete date and time information for the second day of February in the year 2025 at 08:57 AM (IST) according to the ISO 8601 format will be given as 2025-02-02 08:57:00.000. >From https://ontariotraining.net/writing-style-dates/ "If you use the numbered style for dates, you could get into a serious problem: 05/04/11. Is this May or April? British or North American? Write out the dates, and you won’t have a problem." >From https://en.wikipedia.org/wiki/Date_and_time_notation_in_Canada "The YYYY-MM-DD format is the only officially recommended method of writing a numeric date in Canada.[2] The presence of the DD/MM/YY (most of the world) and MM/DD/YY (American) formats often results in misinterpretation. Using these systems, the date 7 January 2016 could be written as either 07/01/16 or 01/07/16, which readers can also interpret as 1 July 2016 (or 1 July 1916); conversely, 2016-01-07 cannot be interpreted as another date." Also read https://www.atlasobscura.com/articles/map-mondays-wtf-is-wrong-with-canada-s-time "In Canada, You Can Just Write the Date Whichever Way You Want" -- You are receiving this mail because: You are the assignee for the bug.
