Apache OpenOffice (AOO) Bugzilla – Issue 76623
Wrong formatting and displaying for some dates
Last modified: 2013-08-07 15:14:24 UTC
Hi, Some dates are formatted as text while other are not. It's may be due to the FR version : To reproduce : - open a new spread sheet - in A1, enter 10/09/1936, it's displayed as a text - in B2, enter 10/09/2007, it's displayed as a date - in C3, enter 10/09/1936, it's displayed as a text - in D4, enter 10/09/1936, it's displayed as a date More, simply copy and past this serie (without past special) 24/10/1984 28/04/1948 07/10/1938 08/10/1938 08/10/1938 08/10/1938 07/08/1938 will be displayed as text. The dates under will be displayed as 07/10/38 until you click on the cell, it will display the right date 08/10/1938. Now copy again the sery and do a past special and choose Date (JMA) in the type field 07/10/38 is now displayed as 06/10/38 but if you click on the cell, the formula bar will display 07/10/38 and =CNUM() will display the good number. One user has confirmed that he can't reproduce it under 2.0.3 but I've not tested. Kind regards Sophie
Hi Sophi, Dutch 2.2 + Dutch language/locale settings, doesn't give problems as you discribe. Cor
Hi Sophie, set the locale and default language to French and can't reproduce. Could you try it again with a fresh Userinstallation ? Are the cells you've typed the dates in are in default format before typing or are they set to text ? Sorry for no better reply. Frank
Hi Frank, The cells I enter the date are in default format. My linguistic options are also default (not set to French) and my installation is clean (this is my working machine :-) I join a spreadsheet where I reproduced exactly the same cases, so before clicking in it, just look at : - cell C9 : the display in the cell is different than what is displayed on the formula bar - cell D4 : look a it, it display 09/09/36 where I have entered 10/09/1936, now click on it, and it will display 10/09/36 - cells A8 to A12 are comporting the same way. Hope this helps Kind regards - Sophie
Created attachment 44663 [details] Spreadsheet reproducing the bugs
Created attachment 44664 [details] screen shot of what is displayed on my Calc
Hi Sophie, installed a French OOo on a Linux system with French locales and tried to reproduce the problem. I was not able to do so. Opened your document and discovered that cells A1, A9 and C3 have an apostrophe in front of the datevalue. If you've typed this, the result is correct as the apostrophe makes a number a string. About the different values shown in the Edit line and the cell, I can't reproduce it, neither with your file nor without. But I had a closer look at the content.xml and found out that the date value is stored correctly but the corresponding <text:p> tag is off by one day. Don't know how this is possible. Could you please move your .openoffice.org2 away and start the OOo again creating a new Userinstallation, it seems to me that there could be a problem with the old one. Thanks for your help. Frank
Hi Franck, I didn't enter the apostrophe, just typed the dates and the apostroph was automatically added (and I know it's the shorcut to enter text formatted numbers ;-). On C3, if you remove the apostrophe, the date is changed to 09/09/36 in place of the 10/09/36 I entered, may the tag is changed here too. In A1 removing the apostrophe doesn't change the display of the date, it's still 10/09/36. I'll test this evening once removing my .openoffice.org2 file, but note that I didn't find the bug, it was reported on our users list by several users under XP and 2.2 but unconfirmed under 2.1 or 2.0.4. And it seems that one user has reproduced it with the dev version of 2.3, but I don't know which one and if he has applied a lang pack, I'll try this evening too with the SRC680_m210 Thanks Kind regards - Sophie
Hi Sophie, thanks for your help. I've seen that you use a Linux box, Ubuntu ? Would be interesting to know what OS'es are affected. So if you could tell us which Ubuntu or whatever Distro you and your customers use it would help a lot. Thanks Frank
Hi Frank (and I've already made that old mistake in your name, sorry) So I've killed my .openoffice.org2 and made the same tests which gave the same results, bad display between formulas bar and cell and wrong formatting as text when entering the dates. My OS is Ubuntu 6.06.1/Gnome and OOF680_m13 Build-1 from Pavel. I'm going to make some tests under windows now on 2.1 and 2.2 and will come back to add my comments. Kind regards - Sophie
Hi Frank, So I've made the test under Windows XP SP2, a fresh install of OOo 2.1 and 2.2 FR just downloaded. - under 2.2, I can reproduce exactly the same issues about formatting and display - under 2.1, I have NOT been able to reproduce one of them Kind regards Sophie
Hi Sophie, no problem with the name at least if you don't think I'm fs. It's also Frank but not me. ;-) I've made my Windows XP as much French as possible and installed the files from the OOo downloadsite. Tried to reproduce the Issue and was not able. The same for an Installation on SuSe10.1 French locales and French Installationset. As you've told me you're using OOFm13 which had some bugs so it does not become the stable version of OOo2.2 and also it's a build from Pavel. Nothing against Pavel and his builds, but could you check it again using the binaries from the OOo site ? Thanks for helping us. As I've said before I was not able to reproduce it, neither does my colleagues. Frank
Hi Frank, I would have been surprised to see a Base specialist here, so no, I'm not confusing with fs but it's an old typo with me :-) I'll try tomorrow with a fresh .rpm install of OOo on my Ubuntu version. However the thread on the fr users list and my tests show that it's reproducible on the stable localized 2.2.0 Windows XP version. Kind regards - Sophie
Hi Sophie, have you checked it again on 2.2.1 ? I was never able to reproduce it, neither on 2.2 nor on 2.2.1 or an src680 build. Thanks for your help. Frank
Hi Frank, It is reproducible with 2.2.1 version under Windows XP (6 of our users have confirmed it). It is not reproducible under Linux. Kind regards Sophie
*** Issue 78199 has been marked as a duplicate of this issue. ***
Issue 75118 seems to be dup also.
I can reproduce this in OOo Calc 2.2.1, though I belive it is a locale problem. W2k SP4 en US-locale OOo Calc 2.2.1 en US-locale After Copy/Paste of the dates mentioned above, the first 2 strings are imported as a(n) (invalid) number (=> therefore text display), while the rest is imported as a date. See attached image. This is probably due to my US-English locale, where 28/04/1948 is NOT a valid date. What locale is your OS? And what locale is your OOo?
Created attachment 46696 [details] Display showing different formatting
discoleo, the problem looks different from what you described - see http://www.openoffice.org/nonav/issues/showattachment.cgi/45745/date-diff.PNG.
1. This issue depends on OS and local time. I saw it on Linux Ubuntu and Wndows XP Pro(see http://www.openoffice.org/nonav/issues/showattachment.cgi/46343/kyiv1.jpg http://www.openoffice.org/nonav/issues/showattachment.cgi/46344/kyiv2.jpg http://www.openoffice.org/nonav/issues/showattachment.cgi/46345/Moscow.jpg) Date 07/10/1938 in Sophie's file is OK in Ubuntu, Kiev Local Time, Ukraine. But when I changed Local Time to Paris, France - I get bug as Sophie described. 2. This bug reproduced if there are more than one cell in document contains date. When we delete all cells exsept problematic cell(for example, C9 in Sophie's file), we get right value in this cell. I could not get problem in Calc's file when only one cell contains date.
Windows XP, same bug on Local Time "Paris". I changed local time only. All other local settings I leaved as they were. In Kiev Local Time, WinXP, it's Ok.
add CC
Hi, er and I have seen it once on a Windows XP with Timezone set to Moscow and locales under tools-options-language settings of an OOo2.2.1 to Ukrainian. Used the data.ods file (http://www.openoffice.org/nonav/issues/showattachment.cgi/46342/data.ods) from Issue 78199. Tried it also with Linux and the same settings, but could not reproduce. Frank
Hi Sophie, Could you please give some information about - what your Windows Regional Settings are - what the Windows time zone is set to - the setting you have in OOo under Tools.Options.LanguageSettings.Languages "Language of Locale setting" Did I understand correctly that you were not able to reproduce the bug on Ubuntu? Did you try the OOo original download, or the Ubuntu provided package? @chkur: you said you saw that on Ubuntu and Windows XP. Your screen shots though show only Windows. Was the error identical on Ubuntu, i.e. with exactly the same dates? Again, did you try the OOo original download, or the Ubuntu provided package? Thanks Eike
2Eike 1.See attachments 2.I saw this bug on Ubuntu with OOo 2.2.0(Ubuntu Provided) and OOo2.2.1Pro("Infra-Resource").
Created attachment 46860 [details] Ubuntu, Moscow Local Time
Created attachment 46861 [details] Ubuntu, Kiev Local Time
@chkur: thanks. May I assume that you're running OOo in an Ukrainian uk-UA locale?
There was "Standard" in OOo settings. I changed it to Ukranian, English, Russian, German and get same result.
Hi Eike, Here is a first feedback from one of the testers : - what your Windows Regional Settings are -->French, France - what the Windows time zone is set to -->Time zone GMT +01:00 Brussels, Copenhague, Madrid, Paris - the setting you have in OOo under Tools.Options.LanguageSettings.Languages "Language of Locale setting" -->Default I'll be able to make some tests on Thursday on windows and linux to provide more feed back. I always use Pavel's .deb package. Kind regards - Sophie
Hi Eike, So to sum up the feedback I've had from our users list : 5 users answered - what your Windows Regional Settings are For 4 of them: Français France, for one of them Français Belgique (Belgium) - what the Windows time zone is set to For all of them: (GMT+01:00) Bruxelles, Copenhague, Madrid, Paris - the setting you have in OOo under Tools.Options.LanguageSettings.Languages "Language of Locale setting" For 4 of them : Français France, for one of them: default Kind regards Sophie
Thanks Sophie, it looks like we can narrow this down to "it somehow depends only on the timezone set in Windows". I'll investigate next week.
Hi Eike, We have been able to reproduce the bug under Mac MacOSX French on OOo m221 aqua+cws aquavcl02, but not on the 2.1 version under x11 still MacOSX French. And also I just reproduce it under Ubuntu and m221, the EN .deb release provided by Joost. My local date and time is Europe/Paris and my default linguistic environment is French. The locale setting for linguistic in OOo is Default. Regards - Sophie
(based on many independent statements setting keyword regression I think this issue really deserves P2 and 2.3 target). Sophie, thank you very much for all the input. You rock!
I also think that this issue need to be fixed as soon as possible. Else it can be even dangerous to use OOo Calc in some cases.
Added MD to cc
Created attachment 47293 [details] 1. Open the Dateshift2.ods file and before doing anything else check the date in A3 as well as the content of the entry-field in the tool bar. As A3 is the selected cell, both places should show the same, namely "01/08/1922". But the strange thing is t
*** Issue 79328 has been marked as a duplicate of this issue. ***
This bug also depends on desktop manager used in Linux. File with bug in Gnome may be opened correctly in KDE, and bug in KDE may be not reproduced in Gnome in the same timezone. Please, say - when this issue may be closed? It really can do much harm and it is unpredictable.
Completing observations from peterlange Fri Aug 3 12:26:19 I can say that what you see depends on the moves of the cell cursor. In the next attachment (written on OOo 2.2.1 Windows XP with fr-FR and Paris time zone) I have formatted as date the cells B1-B6. Then I have typed 14/07/2007 in B2 and 02/12/1930 in B3 to B6. Now with cursor on B1, moving it downwards then upwards on the zone changes both the display of some cells and/or the display in the edit field. You won't believe your eyes.
Created attachment 48629 [details] Cells formatted as date, display changes with cursor move
Created attachment 48632 [details] Composite snapshot of one of my tests, follow the arrows.
Similar problem. The date displayed on the cell is one day less. The date displayed on the formula bar is correct. OS: Fedora Core 6 OO: Version] buildid=680m5(Build:9221) ProductPatch= ProductSource=OOG680 ProductMajor=680 ProductMinor=5 ProductBuildid=9221 AllLanguages=en-US UpdateURL=http://update23.services.openoffice.org/ProductUpdateService/check.Update UpdateID=OpenOffice.org_2_en-US UpdateUserAgent=OpenOffice.org 2.3 (680m5(Build:9221); $_OS; $_ARCH; BundledLanguages=en-US) OOOBaseVersion=2.3 ExtensionUpdateURL=http://updateext.services.openoffice.org/ProductUpdateService/check.Update I'm in Bangalore, India.
Created attachment 48697 [details] OO Calc Display Problem, date displayed on cell is on day less and on the forumla bar is correct
It took about 3 days for our competitior to identify the cause and admit the latest calculation bug and 19 days to issue a fix - see http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/2bca d1a1a4861879/2f8806d5400dfe22?hl=en& http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx We spent 5.5 month just talking about our problem, that is at least as severe. I am hoping that this issue will be fixed before any of our users loose big.
@kpalagin: Please, it doesn't help to compare an everywhere 100% reproducible bug with some weird behavior that occurs only under certain circumstances we had to identify. I'll dive into this next week.
Putting on scuba gear.
Gasp ... reaching surface again. The problem was two-fold, and it depended solely on the timezone setting. One, ICU now incorporates also Olson timezone transitions historical data and even the timezone may differ for different dates. This caused the weird behavior that a date display changed value on cell cursor travels respectively mouse clicks, depending on what the previously set value was. Compensated. Two, it seems that ICU between 2.6 and 3.6 changed ZONE/DST handling, part of the workaround introduced for issue 17222 and issue 24082 isn't necessary anymore but actually made things fail instead. This was the cause of certain date input not being accepted as date. Reworked. In cws dr59: i18npool/inc/calendar_gregorian.hxx 1.14.68.1 i18npool/source/calendar/calendar_gregorian.cxx 1.32.72.1 unotools/source/i18n/calendarwrapper.cxx 1.13.44.1
Hi Eike, I'm sure that you've heard the clap clap of the 70OOO (x2) hands of our Gendarmerie agents when you've reach the surface :) Thank you very much to you and your scuba gear ! Sophie
Thanks a lot! It will be great to use dates in Calc without such unpleasant surprises!
Reassigning to QA for verification. Timezones to reproduce that exposed behavior with one or the other document attached (see descriptions above): Europe/Paris Asia/Calcutta Europe/Moscow Europe/Kiev Asia/Tehran
*** Issue 75118 has been marked as a duplicate of this issue. ***
I was no longer able to reproduce the behaviour therefore found fixed on cws dr59 using Linux, Solaris and Windows build
Thanks a lot for resolving this issue. This issue (http://www.openoffice.org/issues/show_bug.cgi?id=75118) has been there for about 6 months starting at stage 2.2rc1. When can we expect a resolved build to be available for this ? Thanks Sophie for filing a reproducible issue and continually following it up till resolved. Lasantha Marian
*** Issue 83062 has been marked as a duplicate of this issue. ***
Is a patch out for this? The status shows as VERIFIED. Does this mean that a release has not yet been made?
*** Issue 83570 has been marked as a duplicate of this issue. ***
@vhshah: As stated in some comments above (#desc49 and #desc54) the fix was implemented and verified in CWS dr59, which was integrated to OOG680m8 for the upcoming OOo2.3.1, see http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=OOG680%2Fdr59 OOo2.3.1 isn't released yet, but to try out the fix you can download a milestone build at http://download.openoffice.org/680/index.html?intcmp=1235
found integrated on master m239
*** Issue 85128 has been marked as a duplicate of this issue. ***
(Using 2.4m6 on Vista) It turns out that the issue is not fixed - with Russian localle and Moscow time zone Calc autodecrements date 01.07.1919 and earlier. Dates after 01.07.1919 are fine.
Hi Eike, please have a look at this one. Seems a bit different but related. Checked with m5 and m6 entering 30.06.1919 results in 29.06.19 01.07.1919 results in 30.06.19 Frank
forgot to mention that I used Windows XP
@fst: Since this was timezone dependent: which timezone do you use? Europe/Berlin?
Sorry, should had read more carefully.. so, this is about some Moscow timezone.
checked it also with 2.3.1 and found the same behaviour. Seems to be not fixable in time for 2.4. Frank
If we do not fix it for 2.4 then millions of our customers will be exposed to this bug for extra 6 months (since we would not have 2.4.1). As spreadsheets are used in financial calculations the problem might affect somebody financially. As the problem I described seems to be reproducible I would like it fixed in 2.4. But that is just IMHO.
@kpalagin: How many weeks would you like to delay OOo2.4? I won't have time to dive into this for at least the next 2-3 weeks. And please be realistic: how many millions of customers working in a Moscow timezone actually would enter dates before 01.07.1919?
OK, now I have only two questions left 1. Should I close this issue and file a new one just for the newly found case? Or you will retarget this one? 2. Should it ever happen that shown date is different from typed? Thanks.
@kpalagin: Yes, please file a new issue. It should not happen that the date displayed differs from the input date. However, some technical background on why this previously happened on certain conditions covered by this issue, and seems to still happen for the case you found: The ICU calendar we use to input and calculate dates does respect historical timezone data. It seems (didn't investigate yet) that on 1919-07-01 the Moscow timezone either changed its offset value or daylight saving time was introduced with a different offset or starting/ending rule than it is now. A date entered technically is the day at 00:00, but due to timezone and/or DST changes that hour may not be valid, and the calendar adjusts values accordingly. This may result in 1919-06-30T23:00 instead of 1919-07-01T00:00, just for example. While this is technically correct, it is not what the user expects.. Our code compensates for that by detecting the day change and tricking the calendar into a different input adjusted by the offset/DST change, which for some yet unknown reason doesn't work with the specific timezone change you spotted. I'm setting this issue to fixed again, please file a new issue.
Closing.
Eike, thanks for your explanation. Follow-up issue http://www.openoffice.org/issues/show_bug.cgi?id=86094.