In Calc when I type in a date in my default format of DD/MM/YYYY, it returns the correct date unless the first numbe (DD) is less than 13, in which case Calc returns with month and day reversed. For example, I type in 01/04/2013, and it returns 04/01/2013. But, if I type in 14/04/2013, it returns it correctly as 14/04/2013. I just upgraded from a previous version of LO to the newest version in order to correct the problem, but it is still doing it. Note that I have set my default date format as DD/MM/YYYY and have made sure that the particular column format is the same. Also, I have been adding data to this spreadsheet for many years, and the earlier values are still correct. Is there a solution, or is this a bug?
Thanks.
I have set my default date format as DD/MM/YYYY
If that means you have set it in the Windows Regional Settings then it doesn’t have any effect on LibreOffice. The default date format depends on the locale selected under Tools → Options → Language Settings → Languages, Language Of, Locale setting.
A possibility of this switching day/month is that the Date acceptance patterns in the same dialog don’t match the default date format order, for example if there are both M/D/Y;D/M/Y they are tried in that order, for which if the first number is <=12 it matches a month so M/D/Y is accepted as input but displayed as DD/MM/YYYY, but if the first number is >12 it can’t match a month and the second pattern D/M/Y is used. Similar could happen if the pattern includes M/D/Y but not D/M/Y and the locale’s default format is DD/MM/YYYY.
1 Like
Thanks. I was using the default in Tools LibreOffice Calc Calculate Date as 12/30/1899 I didn’t change it, but changed the Local setting in Language Settings from English (Canada) to English UK, and that allowed the first date acceptance pattern to be D/M/Y, and now it seems to work properly. In the past the Canadian format has been DD/MM/YYYY; however, many Canadians have used the U.S. default, and we confused ourselves. I notice now that, in addition to the English (Canada) option of Y-M-D, which I think refects a change to the more international standard, and which caused the confusion, there is an option of “default - English (Canada)” which is the D/M/Y that must have been in earlier versions.
So, on on 01/07/2021, I’ll remember not to step outside in my shorts after many months of quarantine and expecting a hot summer day but walk into a blizzard.
The “Default - English (Canada)” tells that you’re using the default locale that matches your current system locale, in this case en-CA. It’s not a “default en-CA” vs “non-default en-CA” (i.e. different) locale. The predefined date acceptance patterns for en-CA are Y-M-D;M-D;M/D/Y;M/D so that would explain what I wrote in my answer, the M/D/Y input displayed as DD/MM/YYYY if first number <=12.
The LibreOffice “English (Canada)” date formats have not changed since years. The default date format is YYYY-MM-DD since 2010.