Apache OpenOffice (AOO) Bugzilla – Issue 15773
Currency symbol left justified, while amount right justified in a cell (accounting notation).
Last modified: 2017-10-17 18:34:19 UTC
It's a very clean way of displaying columns of currency amounts. Just like the "Accounting" cell format in Excel (I hate to say it, but... :) In Excel when a column is formated as Accounting, the currency symbols in all the cells are aligned to the very left of the cell, while the amounts are aligned to the decimal separator symbol of the amounts. Several customers have asked for this already, but I guess they'll have to wait for now. :( Anyway, the format code for doing this in a cell in Excel is: _ $ * #.##0,00_ ;_ $ * -#.##0,00_ ;_ $ * "-"??_ ;_ @_ (using comma as decimal separator) When imported by OOo 1.1beta2 this format code translates to: _ "$"\ * #.##0,00_ ;_ "$"\ * \-#.##0,00_ ;_ "$"\ * "-"??_ ;_ @_ but it just displays the currency symbol a character to the left of the amount as standard. No way. It'd be great if this could be done in Calc also. Please do what you can about it. Thanks!!!! Gabriel Gazzán
Hi Bettina, this is important for improving the xls-filter (and an often mentioned missing feature)
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged issue. If you confirm with the target OO.o 2.0, then please keep it on your owner (or the owner of the concerning developer) for implementation. In case you want this issue for 'OOo Later', then please reset the target milestone. If you decline the issue finally, please set the resolution to 'Wontfix' (but do not close). In case of 'OOo Later' or 'Wontfix' please reset it on Bettina's owner. Thank you.
This is not on the list of features that we're going to do for 2.0. We need to focus on the enhancements from the PCD document.
*** Issue 16451 has been marked as a duplicate of this issue. ***
*** Issue 36318 has been marked as a duplicate of this issue. ***
*** Issue 36943 has been marked as a duplicate of this issue. ***
I also would really like to see this feature added to OO Calc!!! :-) This makes columns of currency figures of variable length easier to read. Thanks, Tracey
*** Issue 57758 has been marked as a duplicate of this issue. ***
I just want to affirm the gravity of this particular missing feature. It affects the representation of financial figures and the psychological impact is high: "This software is unable to show my accounting in a pleasant manner."
I also would like to see this feature added in next version of OO Calc. Pls we need it. Tnx
This issue affects the ability to properly import and export spreadsheet to Excel. It should be added as a additional format type in Calc. This issue has been kicking around for a long time and should be addressed soon.
Change version so it isn't lost
Please don't change the Version fiels as it indicates the first occurence of the Issue.
okay. Just didn't want it to disappear like so many others.
Could you please revise the "Target Milestone" field to at least "OOo 2.x"? OOo LAter, makes it seem it has no importance. Thanks.
oh... I see that isn't implemented yet... I think it's a very usefull option... please implement it!
This issue has been pending for a while now. I can hardly imagine that it is a big deal to fix it (for people who are already into OOo development, of course). IMHO it is a bad idea not to fix it, just because there are only 26 votes. A lot of people will just not take the time to find the issue in the bugtracker, sign up and vote for it. They will see that the format is awful (sorry, but it is!) and either accept it or switch back to Excel. The possibility to add an IF-formula next to the number is not a solution, it's a workaround, and a bad one. Best regards
I can't believe that this still hasn't been fixed. It is a *major* problem for OOo acceptance in business-oriented environments. Please fix this for the upcoming OOo release. While you're at it, please add a default number format for putting negative currency numbers in parentheses. This is a widely-used convention in accounting and financial reporting. Although an experienced user can define this him- or herself as a custom number format, newbie users expect to see this as one of the pre-installed format options and are discouraged when they can´t find it. In order to be accepted in corporate environments, OOo needs to make a strong commitment to providing standard business and accounting functionality to its users.
The most heard-of complaint about OOo in my company where we're switching to OOo, is this issue. We're using spreadsheets for budgetting. It's very ugly, all these misaligned euro signs :( Please fix this, people need this feature! regards
I can't understand what is taking so long to fix this. This is not a hard code to write. I understand that Dell is considering putting OpenOffice on their systems. Now how happy do you think people will be when they realize that this feature is unavailable? Do you really think they will continue to use your product. I happen to love OpenOffice but this particular lack of an important feature is really bugging me. It makes my spreadsheets look sloppy and unprofessional. In turn, it makes me look sloppy and unprofessional. What do we have to do to get this done? Come on people. Give your customers what they want.
Hi Niklas, these RFEs are in your ownership.
I was hoping that the newest release would have a fix for this problem. I cannot believe that this has not been taken care of yet. What seems to be the holdup? Come on people -- you want Ooo to be the best, now make it that way.
This is the major obstacle at out company to implement OpenOffice... We do all our budgetting in Excel and really need this feature to switch to OO.o
Added (accounting notation) to the summary
@rmax Issue 15756 - enhancement request for pre-defined number format for displaying negative numbers in brackets.
I can't get my company to convert to OOo before this is in place ! I started but man did I get bad feedback on this ugly formating on currency ! Had roll back to MS Office again :-( Please add this to OOo soon.
There seems to be a lot of people requesting in this issue but not very much indication of what attention is being afforded to it. If this issue blocks widespread acceptance by firms (for accounting, et al), as I imagine it does, why has there not been more noise about it over the five years since this issue was first submitted? Do any prominent companies, like Sun Microsystems, use OpenOffice (StarOffice) Calc for purposes that would be affected by this issue? If so, do they just bear with the poor formatting? Do they use some kind of workaround? Have they fixed the problem internally? If they do not use OpenOffice Calc for any purpose, does that reflect on the program or are there extenuating explanations? OpenOffice 3.0 beta has been released, but I can see no indication of this issue being yet addressed. Should there be any consideration whether this issue could be a candidate for the final release of OpenOffice 3.0 to improve its desirability to accountants?
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project Therefore adjusting target to 3.x.
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project
Hi OOo team, OpenOffice is our company default across Australia, and the Calc enhancements in recent versions have helped significantly with the migration process ...... but (there is alway a but!) ..... the lack of the Accounting format is generating some push-back from the Executive ranks and a resolution on this issue would be very helpful in utilising OOo in a business environment. Finally, Thanks for a job well done thus far, and keep up the excellent work :)
Hi! Does anyone know if this issue have been solved in 3.0? Does anyone have a workaround for this issue? Thanks. Junior Polegato.
I installed OO.o 3.0 hoping to discover that this issue was resolved. But no luck. This issue has been open for five years now. Any chance that it will ever be implemented? Unfortunately I still depend on Excel for budgetting... :-(
We also waited until the OOo3 release to see if this problem was resolved (though file-locking issues in OOo3 have put it on hold anyway).
Any news from anyone about this issue? Been over 6 years now, and a bit quiet. The quarterly review user fl points to does not seem to be updated after Q2-2008 either: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project Also, http://wiki.services.openoffice.org/wiki/Calc/To-Dos does not mention any newer quarterly reviews. Seems a bit strange, as it is called *quarterly*. I hope the planned version 3.x does not mean 3.9 ;)
*** Issue 107770 has been marked as a duplicate of this issue. ***
*** Issue 110774 has been marked as a duplicate of this issue. ***
Any good news for this issue???
very useful format
Seven years! What does one have to do to get a relatively easy addition put in place? I wish I understood how to do this myself; I'd quickly do it. I've looked at the code, and cannot figure out how to make a particular format render. I can find how to add formats to the format list, but can't find where you render a particular format for display. This should affect spreadsheets, Writer tables, formatted fields on Base, and the ReportWriter.
I am so disappointed in the lack of being able to resolve this issue. I would think that this would not be hard to fix. It seems to me that someone has put this on the back burner hoping it will go away. Well, it will not. My spreadsheets look unbelievably sloppy and unprofessional. I would hate to go back to Microsoft Office based on this one issue, however, it is an important one to me and a lot of other people. The people that I have suggested OpenOffice to have switched and then switched back again because of this one issue. I believe that you are losing more customers than you should be because of this. I get the impression that more emphasis is being put on the fact that you cannot change the colors of the tabs. Although that is nice, it does nothing to make your spreadsheets look more professional. 7 years! Good night 7 years and this still is not fixed. Someone better wake up and fix this.
According to the comment by rsteinmetz70112 on 2006-06-26, "This issue affects the ability to properly import and export spreadsheet to Excel." In addition, we have people reporting in this bug that they cannot use OpenOffice.org Spreadsheet in a PROFESSIONAL capacity BECAUSE of this issue. Given these reports, perhaps it would behoove the devs to increase the priority of this bug to P2 -- if nothing else, to give it higher visibility. It would certainly send the message that OpenOffice.org does take its business users seriously, as I am sure that it does. Quickly querying the Spreadsheet bugs shows me that there are 86 open reports at P2 priority, 8 of which are of type Enhancement: 4 New, 3 Unconfirmed, and 1 Reopened. There are 70 Defect reports. By contrast, there are 2,850 bugs at P3 priority.
This would be a useful feature.
This is ridiculous. SEVEN YEARS later, and not even any initial move to fix this?
@ j_porter: well, there are always more wishes than hands. A pity, indeed. But ridiculous... Maybe you can show where you contributed with the many tasks, for many different skills, in the community, before choosing such words?
@cornouws: I think your indignation is rather misplaced. Most of my participation and contributions to the open source community have been on other projects, but I don't see how it's relevant. Thanks for trying to make it a personal issue though, that's very professional of you. On other major open source projects that use public bug trackers, like the linux kernel project, or x.org, or Ubuntu, there is almost always some sort of public feedback from the primary developers on the status of issues that are extant but either cannot be fixed due to other blocking issues or will not be fixed (yet) due to time or resource limitations. You should go back through and read the comments in this bug report and look at the dates as you go... the sound of crickets is strong on this one. Remember, using a public tracker is not only intended to help solicit assistance from the community, it is also intended to build public confidence in your product and the methodology that you use to refine and improve it. It's a two-way street. If the primary developers aren't willing to respond publicly and constructively to bug reports, then much of the intended transparency is lost, and over time so will the public support and goodwill that are necessary for these kinds of projects to exist. This is especially important on long-standing bug reports that directly affect the project's viability for real world (commercial) adoption, like this one. Back to the topic at hand... if you'd like to point out where in the enormous OOo codebase these kind of cell display rules are located, I'm sure there are a nontrivial number of devs in the public community, myself included, who would be more than happy to take a look and try to assist.
The number formatting code is at svl/source/numbers (svtools in older versions). But it converts a number into a string, with no additional alignment information. So this would require some closer coupling between number formatting and Calc cell output, not just implementation of another formatting rule. Issue 46511 (automatic decimals) was somewhat similar in that regard, but limited to a single format.
@j_porter. The fact that I do not like using words as 'ridiculous', has nothing to do with my voluntary involvement in the community. Wordings that possibly look as accusing of deliberate bad intentions, are not justified, IMO, although I understand that it can be caused by irritation. Asking for pointers, explanation, as you did in your second comment, is different. Also I have no objections against suggestions on how the OpenOffice.org community can improve. Reality is tough though, so contributions beside suggestions are welcome too. Thus: thanks for your reply :-) Cor
This issue is proof that things are in disparate shape over here. I don't know very much about the OpenOffice community yet, but if this bug is any indication, things are in really bad shape. What are you people arguing over? Surely not the fact that this bug is vitally important... because if there was any doubt, I'm here to reassure you that, as an average user of OpenOffice and a former user of Calc (who wishes to become one again), this feature is extremely important and should get immediate attention above all else-- even bugs! I can deal with bugs, but this leaves me with no options aside from switching back to a closed source program. And I'm using Ubuntu, so that means I have to set up Vbox and involves a ton of work-- if I had any alternative, I wouldn't switch. When I'm not using it for anything important, I'll still use Calc. But because this has been “sitting on the self” for many years now, without apparently even a glance from the developers (who I'm starting to think may not even pay attention to anything other than Writer...) Calc is next to useless for me. Summing up this comment in one sentence: I can confirm this issue!
When this feature will be brought to the next version? Apple can do this job since iWork '09 by simply clicking on ONE button in the information window for a single or multiple cells. I can't understand why such a simple but very use full and often needed feature was left out for such a long time!!! So please, do your good job once again and add this feature! :)
Created attachment 80081 [details] For your interest how it simply works by using Apple's Numbers This attachment shows how simply the "Buchhaltungsstil" feature can be used, by working with Apple's iWork '09 Numbers! I'd prefer to use OpenOffice but with the same simply used feature for private and commercial, professional tasks! Thanks!
I'm new to spreadsheets, but did some BASIC programming many, many years ago. Here's what I came up with...I know it's a bit long winded, but it seems to work well from initial tests. I've never used Excel so I can't compare the results. Please let me know if it works for you. =CONCATENATE("£";REPT(" ";10-LEN(TEXT(F3;"#####0.##")));TEXT(F3;"##0.##"))
I forgot to add, this needs to be used with a monospace font such as Courier New, unfortunately
Most of us have at least some programming skills. IMHO, what you are proposing is not even a workaround, as most people would probably not want their spreadsheets printed in old typewriter style. Let's hope that there will eventually be a solution to this problem.
Created attachment 83831 [details] screenshot of imported Excel accounting format
I just added a screenshot of LibreOffice 4.2.4.2 that comes with Ubuntu. It now seems to preserve accounting formatting as imported from Excel 2010.
I may actually purchase MS Excel after reading this thread. This is a very necessary feature.
(In reply to ilana from comment #56) > I may actually purchase MS Excel after reading this thread. This is a very > necessary feature. First I would try LibreOffice if I were you. Since version 4.0 the notation is correctly displayed (1); adding format to the currency list on the to-do (2) for some time. Ciao - Cor 1) https://bugs.documentfoundation.org/show_bug.cgi?id=34943&redirected_from=fdo 2) https://bugs.documentfoundation.org/show_bug.cgi?id=63536
Reset assigne to the default "issues@openoffice.apache.org".
*** Issue 16819 has been marked as a duplicate of this issue. ***
Hi everybody and specially you Bettina, Since years, every time I put numbers with variable number of decimals in column, I regret not to be able to align them on the decimal separator. Surprisingly, it's possible with Text ans not with Calc. Is there any problem that could explain the stand by of this issue ? May we hope the implementation of this enhancement ? Cordialement - Antonin