When using the basic function Format in a macro (for Writer) and other locale language settings than English it gives me wrong results. Macro: sub addDateText dim document as object dim dispatcher as object document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dim sText as string sText = Format(Now(),"dd/mm/yyyy") dim textFormatat(0) as new com.sun.star.beans.PropertyValue textFormatat(0).Name = "Text" textFormatat(0).Value = sText+CHR$(9)+"[SomeText]" dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textFormatat()) end sub With locale set to ro-RO: 07.04.2017 [Some text] With locale set to en-US: 07/04/2017 [Some text] With locale set to en-UK: 07/04/2017 [Some text] With locale set to fr-FR: 07.04.2017 [Some text] With locale set to de-DE: 07.04.2017 [Some text] after modifying sText = Format(Now(),"dd\/mm\/yyyy") it produced the desired results. I've tried but didn't work changing the Date accepted patterns in Options->Language Settings->Languages.
I can confirm this on Windows 10 64 bit, LibO 5.1.3.3, with the exception of fr-FR where the slashes are used in my instance (in accordance with my hypothesis stated below: slash is the fisrt separator; in contrast to fr-CA where the hyphen comes first). A shorter way to show the effect is to execute simply msgbox Format(Now(),"mm/dd/yyyy") But I'm not sure whether this is a bug or it is intional. My hypothesis, based on incomplete tests, is that LibO replaces the slash by another separator if a different one is to be found in the first date acceptance pattern for the locale used (it seems to be depending only of the first separator in the format parameter, by the way). Perhaps the slash is regarded as the standard separator, and is only replaced if there is another principal separator for the locale setting. As the documentation is not detailed enough, one can only be sure by looking at the code, which I cannot. As far as I know, date acceptance patterns are entered into the code of Lobo, so it's no wonder you could not change them. As the problem can be easily overcome by using the backslash, as you found out by yourself, I would propose that you set the importance to "minor". Gerhard
That is expected. Separators are exchanged with those of the output locale, so for example for the format "0.00" a value 123.45 is displayed as "123,45" if the locale uses a comma as decimal separator. The canonical way to force a string instead of a separator *is* to quote it, e.g. by preceding it with a \ backslash. However, for date formats the result can be misleading if the date order is not identical between locales, e.g. MDY in en-US vs DMY in de-DE. In the example the date for de-DE should be displayed as 04.07.2017 instead. This is the actual bug encountered. Date acceptance patterns are completely unrelated to this, they determine what input is accepted as date when parsing a string.
If I understand Eike's answer correctly, then the slash in the second parameter of FORMAT doesn't mean the character "slash", but represents the separator between day, month and year, which is changed to the local separator according to the locale used, and this will only work correctly, if you use the order of day, month and year which corresponds to the locale. Indeed, Format(Now(), "DD/MM/YYYY") yields 13.04.2017 with german locale, whereas Format(Now(), "MM/DD/YYYY") yields 04/13/2017, with correct numbers, but another separator. In my tests I found out that you often can use period or hyphen as separator, as well, but this does not work in all cases. So the recomendation wold be to use the correct separator *and* the correct sequence fitting your locale for the format in accordance with the local, or quote the character you wish to force withe backslash. If the separator used is not connected with the date acceptance pattern, it must be defined within the locale. And it will normally coincide with the first date acceptance pattern for a locale, so it's no wonder I didn't find a case contradicting my hypothesis. I will propose an addition to the help text in a new bugzilla entry. @Adrian: I think your bug can be regarded as resolved, but I leave it to you to change the status accordingly.
Ok, now I understand this is how LO works and if I want to insert a custom separator I need to put backslash char in front of it. I was accustomed to MS (till recently when my company adopted the LO) and it didn't need a "special" char to display a custom separator so from there it generated my misunderstanding. I have to unlearn some things and learn another. Thanks for your answers.
This is not resolved though, I'm keeping the bug for the wrong date order if the actual locale one's is different from MDY (which it usually is). Bear in mind that the BASIC locale is en-US and formats are converted to the output locale. As a side note, if you want locale independent date formatting use the ISO sequence YYYY-MM-DD (In reply to Gerhard Weydt from comment #3) > If I understand Eike's answer correctly, then the slash in the second > parameter of FORMAT doesn't mean the character "slash", but represents the > separator between day, month and year, which is changed to the local > separator according to the locale used, Yes. > and this will only work correctly, > if you use the order of day, month and year which corresponds to the locale. Which is the bug here. > Indeed, Format(Now(), "DD/MM/YYYY") yields 13.04.2017 with german locale, > whereas Format(Now(), "MM/DD/YYYY") yields 04/13/2017, with correct numbers, > but another separator. ? I get 04.13.2017 (i.e. separator adapted but the wrong order).
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=51478cefaa4e265b42e3f67eda0a64767ff3efba Resolves: tdf#107012 follow date order of the target locale It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a4c9e4539ee82c409add42e68329d4eb1100d62f Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ce8689eaa9cab85193d031b911331c9675b77bc&h=libreoffice-6-1 Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up It will be available in 6.1.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=16ee6373bab9056650cf3b7a153e832d8d09fc28&h=libreoffice-6-0 Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up It will be available in 6.0.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.