Error in Calc custom formats between closing and opening a file

users
#1

Hi All,

I'm running LibreOffice Version 4.0.3.3 (Build ID:
0eaa50a932c8f2199a615e1eb30f7ac74279539) on Windows 7 64-bit. Files are
saved in native ods format.

I'm using a custom format code on a cell in Calc to get a value
displayed as a rate per hour.

This is the format code I want:

[$R-1C09] #,##0.00 " / hour";[$R-1C09]-#,##0.00 " / hour"

As it is, that code works fine. When I enter a value of 300, I can use
it in calculations, and it gets displayed as "R 300 / hour", just as I
want.

The problem comes in when I close the file. Even if all I do is enter
the correct formula as above, then save and close the file, when I
re-open the file, the field now displays as "R 300.00 / hoRr". When I
go to the cell format, I see the format code has changed to:

[$R-1C09] #,##0.00" / hRRr";[$R-1C09]-#,##0.00" / hoRr"

This happens every time I close the file. When I re-open it, the format
has changed. This is not caused by the save, because once edited, the
formula stays correct for the duration of the session, until I close
the file.

It's not just that "hour" has changed to "hRRr" and "hoRr", but the
space between the "0.00" and the quote has gone (in fact, the quote now
has two spaces after it, instead of the one before I closed the file).
This space is very important. If I edit the formula as it is, and just
change the "hRRr" to "hour", the format code is removed. On the
previous version of LibreOffice I had (not sure which version that was,
though, either 3.5 or 3.6 something), it would have no effect. After
editing the format code, the value would still be displayed wrong, and
going back to the cell format, I would see that the format code had
reverted back to the wrong code. Only if I put a space between the
"0.00" and quote would the code retain the "hour". Now, on this version
of LibreOffice, if I don't put a space between the "0.00" and the
quote, if I just change the "hRRr" and "hoRr" to "hour", the custom
format code is entirely removed, and the format reverts to a standard
number. If I correct the format, it stays until I close the file, and
then once I re-open it, it has reverted to the incorrect custom format
code.

I have had this issue in several files, and can re-create it by opening
a new file, putting "300" in the first cell, going to "Format | cells"
choosing currency and putting ' " / hour"' after both "0.00". This
shows correctly. Then I save the file, close it, and re-open it and it
has mangled the custom format code.

Can anybody else confirm that they are also having this problem? Does
anybody know anything about this problem? Should I submit a bug for
this problem, and if so, where? At https://bugs.freedesktop.org/ or at
https://www.libreoffice.org/get-help/bug/ ? I have tried searching both
the mailing list archive and the bugtracker for this problem, but
didn't turn up anything relevant.

Regards

Paul Steyn

#2

Bump?

Or is this the wrong mailing list to ask such questions on?

#3

Hi Paul,

Paul schrieb:

Bump?

Or is this the wrong mailing list to ask such questions on?

It is the right mailing list. But sometimes an email slipped through.

I cannot reproduce the problem with LibreOffice 4.1. Please try it with the LO4.1 beta.

If you see the problem there too, it might defend on the locale and you should tell us your language settings in that case.

Kind regards
Regina

#4

Hi Regina,

Thanks for your response.

If I install the 4.1 beta, will it install side by side with the
existing stable, or replace it? If it replaces it, can I revert to
stable easily? Do I just re-install with the stable download?

And how do I determine which locale and language settings I use? Do I
simply go to Tools|Options|Language Settings|Languages and give you all
the values there?

Thanks again

Paul

#5

Hi Paul,

Paul schrieb:

Hi Regina,

Thanks for your response.

If I install the 4.1 beta, will it install side by side with the
existing stable, or replace it? If it replaces it, can I revert to
stable easily? Do I just re-install with the stable download?

It will not install side-by-side but will replace your stable 4.0.

You can install it side-by-side using an administrative installation:
run cmd to open the command window.
change directory to where the xxx.msi file is.
run msiexec /a xxx.msi

Of cause xxx is the longish file name. You can copy and paste the longish file name in the command window by using the context menu.

After the files are extracted, open the file bootstrap.ini in the LO program folder and change the line to UserInstallation=$ORIGIN/..

And how do I determine which locale and language settings I use? Do I
simply go to Tools|Options|Language Settings|Languages and give you all
the values there?

Yes, and please the language of your Windows too.

Another idea, try, whether it works, if you do not use currency in addition but only add your text.

Kind regards
Regina

#6

Hi Regina,

Terribly sorry about the delay in following up on your suggestions. I
didn't mean to just disappear like that. It's been a bit hectic here,
and I haven't had time to try with LibreOffice 4.1 beta yet, but I've
tried a couple of other things.

Firstly, I tried formatting the cell as a plain number, with
' " / hour"' after the default format, so the complete format is
#,###.00 " / hour"

This got changed over the save to
#,###.00" / hour"

Again, the space was removed in front of the quotes, and added
behind. Other than that, the "hour" wasn't mangled, I'm assuming that
the mangling is only because of interpreting it as currency symbols.

When I removed the extra space behind the quotes, but left it without a
space in front, it preserved that over saves, i.e. the following worked
fine over saves
#,###.00" / hour"

This is still rejected when I change to currency format.

Next, I looked at my locale settings. Under Tools|Options|Language
Settings>Languages I have the following:
Language of
    User interface: Default - English (USA)
    Locale setting: Default - English (South Africa)
    Decimal separator key: checked : Same as locale setting (.)
    Default currency: Default - ZAR
    Date acceptance patterns: Y/M/D;M/D
Default languages for documents
    Western: English (South Africa)
    Asian: greyed out : Default - Chinese (simplified)
    CTL: greyed out : Default - Hindi
    For the current document only: not checked
Enhanced language support
    Show UI elements for East Asian writings: not checked
    Show UI elements for Bi-Directional writing: not checked
    Ignore system input language: not checked

I tried changing the default locale setting to "English (USA)", which
also changed the currency and date settings. When I added ' " / hour"'
it again moved the space from in front of the quotes to inside the
quotes over saves. But now this doesn't mangle anything, I'm
guessing because it's using a dollar sign instead of an "R" as the
currency symbol. Changing the symbol to "R" by changing the format code
from "[$$-409]" to "[$R-409]" works if the locale is "English (USA)".
When the locale is "Default - English (South Africa)", changing the
currency symbol by changing "[$R-1C09]" to "[$$-1C09]" doesn't help
matters.

I also noticed that with a space either in front of the quotes or
moved inside the quotes to give a double space before the slash, two
spaces are shown before the slash in the actual cell. So the only way
to get only one space between the number and the slash in the cell is
to have only one space, either in front of the qoutes or inside the
quotes, but not both.

It feels like there are two bugs, the first is that over saves LO
doesn't preserve the space before the quotes, and moves it inside the
quotes, irrespective of locale, and the second is that when there is no
space before the quotes, the letters inside the quotes get interpreted
as control codes instead of as literal text, and this interpretation
depends on locale.

There was also some strange behavior when entering just a plain number
format, not currency. If I tried to enter a new number format by adding
spaces inside the quotes, without a space in front of the quotes, say by
setting the format to '#,###.00" / hour"' and then adding spaces to
make it '#,###.00" / hour"', the moment it became a new format (one I
hadn't used before), the preview changed to just displaying the number,
without any formatting, and if I clicked OK, this carried over to the
cell and reset the format to '#,##0 ;(#,##0)'. If, however, I didn't
click OK when the preview reset, and instead added a space before the
quotes, the preview suddenly became correct, and I could then remove the
space in front of the quotes and it would stay correct. Once I pressed
OK this format would get saved, and I could use it again without it
getting messed up. Likewise if I added a format with, say, 5 spaces
in the quotes and no space before the quotes, in this way, then went to
cell formatting again and removed a space to four spaces, this didn't
get messed up. But if I removed another space, to 3 spaces, which is a
format I had previously used, then added a space, the format would get
messed up again. It seems that there is some format interpretation
going on behind the scenes when a space is added that is not going on
when a space is removed.

I think someone needs to have a careful look at how this section of the
code is operating, there are subtle bugs in it.

I will try to install the beta as soon as I can, and will test again
and report back.

Thanks again for the help

Paul

#7

I sent the following, but apparently not to the list:

Well, if all you need is to manually copy and paste, then copy the cells, move to the other spreadsheet, then select the starting cell, select Edit->Paste Special, then uncheck the Formulas option (you may first need to uncheck the Paste All box first). This will paste the data and not the formula.

Carl

#8

Arrgghhh. This was supposed to be a reply to the "Re: Info from 3 cells merged into 1" thread. Sorry!

#9

Works perfect - thanks!

Marino

-----Ursprungligt meddelande-----

#10

Hi All,

Haven't had a chance to test in LO 4.1 beta, but I was hoping someone
could at least confirm that they are getting the same results in 4.0 or
prior, and maybe even someone with 4.1 could comment on if it is still
happening.

Anybody?

Regina, you said you weren't getting the weird formatting in your
version, but can you confirm the behaviour as regards the moving of the
space in your version?

Many thanks

Paul

#11

Hi Regina, All,

I've finally (finally!) updated LO, and checked this problem again, and
it still manifests. I can confirm that it manifests with the following
LO versions:

4.0.3.3
4.0.5.2
4.1.1.2

This is with a locale of South Africa, but I think that just makes the
problem worse by confusing some of the characters in the string "hour"
with locale specific currency symbols; the underlying problem has to do
with not preserving the space before the quoted string literal in the
format string over saves, which is not a locale specific problem, as far
as I can see.

Can anybody confirm this and give their LO version and locale?

Should I file a bug report, and if so how do I go about doing that?
When I first looked at filing a bug, I found both
https://bugs.freedesktop.org/ and
https://www.libreoffice.org/get-help/bug/. Which is the currently
preferred bugtracker?

Thanks for the assistance so far

Paul

#12

Hi,

I'm running LO 4.1.1.2 (Document Foundation Version) in Ubuntu 13.04. After reading about this issue, I tried some spreadsheets going back a number of years, mostly .ods, and one .sxc, and all of the formulas saved in them are working perfectly. This is in the US. I also tried some new formulas using existing data in the spreadsheet, and they work fine also.

Don

#13

Hi Don,

Thanks for the input.

Is this also using a string literal after the format string, like " /
hour" ? And if so, even if the formatting isn't messed up, is there a
space between the number specifier and the string literal, like
#,###.00 " / hour"

or has the space been put inside the string literal, like
#,###.00" /hour"

I suspect the space issue is not region specific, but just isn't
immediately apparent on US versions, so would be interested to hear if
it is happening or not with your documents.

Thanks

Paul

#14

Hi Tom,

Thanks, I think I'll try add a bug report about this. Was hoping to
have some verification of this error, but it doesn't seem to be
something many people have run into.

Paul

#15

Hi :slight_smile:
The 2nd one
https://www.libreoffice.org/get-help/bug/
(without the dot at the end) leads to the first one but just helps you collect useful information for it.

So, either is fine. 
[Edit]  oops, that 2nd one seem to be down right now but might well be fixed by the time you look at it
Regards from
Tom :slight_smile: