Dutch postal codes comprise of four digits followed by two characters. In Libre Office these postal codes are routinely mistaken for time formats when they end with AM or PM. How to reproduce: - Enter a dutch postal code ending with AM or PM (e.g. 5401 AM) in a cell. Expected behaviour: - The cell will display "5401 AM". Actual behaviour: - The cell will display "5401:00:00", some kind of time format. Note that in the Netherlands 24-hour time formats are used, without AM or PM. Note also, that any valid time entry using AM or PM will use a number <= 12, and certainly not a number >= 1000 like in the Dutch postal codes. This behaviour occurs was observed in several places in Libre Office, not just in Calc: 1: Entering a postal code in a cell in Calc See example above. 2: Entering a postal code in a table in Writer, when "Number Recognition" is on. The error is not immediately obvious here, but including these cells in ranges e.g. for sums will produce unexpected results 3: Importing data from CSV files into Calc Workarounds exist in this case, but most Dutch users will be unaware of the problem and the workaround. 4: Importing data from CSV files for a mail merge into Writer In the main menu, in the dialog under Insert | Fields | Other, CSV files can be added as a database source in the tab "Database". LO uses the first line / record to try and automatically determine the data types of the fields in the CSV file. When that first line contains a postal code including AM or PM, things go wrong in the merge. There are workarounds for this, but most Dutch users will be unaware of this problem buried deep inside LO.
@eike: you recently blogged about data acceptance patterens. Is there also some function where exceptions/situations as the one in this issue can possibly be handled?
Any value >12 with AM/PM can't be a clock time, I think this should be pretty straight forward fixable.
Resolved on master with http://cgit.freedesktop.org/libreoffice/core/commit/?id=803b5513eff8f8c185a91e91aee235dfab38d3bc (mentioning wrong bug id in commit summary)
(In reply to comment #2) > Any value >12 with AM/PM can't be a clock time, I think this should be pretty > straight forward fixable. Thanks ! (and I see you already noticed that 46233 was the wrong one..)(In reply to comment #2) > Any value >12 with AM/PM can't be a clock time, I think this should be pretty > straight forward fixable.
Great! Nice to see this fixed. 2012/5/10 <bugzilla-daemon@freedesktop.org> > https://bugs.freedesktop.org/show_bug.cgi?id=47149 > > --- Comment #4 from Cor Nouws <cno@nouenoff.nl> 2012-05-10 07:35:12 PDT > --- > (In reply to comment #2) > > Any value >12 with AM/PM can't be a clock time, I think this should be > pretty > > straight forward fixable. > > Thanks ! (and I see you already noticed that 46233 was the wrong one..)(In > reply to comment #2) > > Any value >12 with AM/PM can't be a clock time, I think this should be > pretty > > straight forward fixable. > > -- > Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email > ------- You are receiving this mail because: ------- > You reported the bug. >
I'm sorry to reopen the issue, but: (again, in the Netherlands) Postal codes 1000 AM up to 1259 AM and 1000 PM up to 1259 PM do exist. So, with filtering if the first 2 digits are larger than 12, approx. 97% of the incorrect interpretations are solved. Couldn't it be an idea to use the language setting of the document, i.e. when set to dutch, AM/PM representation of a time is not used?
One possible problem to avoid when checking for a Dutch language setting is with importing csv files. The Dutch language setting is avoided because it includes "," as a decimal separator. However, in Dutch engineering practice, the use of "." as decimal separator in csv files is widespread and unavoidable. Therefore, most csv files need to be imported using en_GB or en_US language settings. These files may also include postal codes. So, checking for a Dutch language setting to avoid misinterpretation of postal codes may create new problems. 2012/5/11 <bugzilla-daemon@freedesktop.org> > https://bugs.freedesktop.org/show_bug.cgi?id=47149 > > Winfried Donkers <osc@dci-electronics.nl> changed: > > What |Removed |Added > > ---------------------------------------------------------------------------- > Status|RESOLVED |REOPENED > Resolution|FIXED | > > --- Comment #6 from Winfried Donkers <osc@dci-electronics.nl> 2012-05-10 > 22:48:58 PDT --- > I'm sorry to reopen the issue, but: > (again, in the Netherlands) > Postal codes 1000 AM up to 1259 AM and 1000 PM up to 1259 PM do exist. > > So, with filtering if the first 2 digits are larger than 12, approx. 97% > of the > incorrect interpretations are solved. > > Couldn't it be an idea to use the language setting of the document, i.e. > when > set to dutch, AM/PM representation of a time is not used? > > -- > Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email > ------- You are receiving this mail because: ------- > You reported the bug. >
(In reply to comment #6) > I'm sorry to reopen the issue, but: > (again, in the Netherlands) > Postal codes 1000 AM up to 1259 AM and 1000 PM up to 1259 PM do exist. 1000 is still greater than 12, no?
(In reply to comment #7) > So, checking for a Dutch language setting to avoid misinterpretation of > postal codes may create new problems. There is no check for language in this case, AM/PM are treated generally.
Perfect! 2012/5/11 <bugzilla-daemon@freedesktop.org> > https://bugs.freedesktop.org/show_bug.cgi?id=47149 > > --- Comment #9 from Eike Rathke <erack@redhat.com> 2012-05-11 03:00:15 > PDT --- > (In reply to comment #7) > > So, checking for a Dutch language setting to avoid misinterpretation of > > postal codes may create new problems. > > There is no check for language in this case, AM/PM are treated generally. > > -- > Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email > ------- You are receiving this mail because: ------- > You reported the bug. >
Tested on master (Build ID: 7327195) for cases 1, 2 and 3 from the description. Resolved indeed! Nice.
USPS offering excellent services. Now all USPS employees can make use of liteblue portal to get all the benefits. Login to it from https://liteblue.onl/