Issue 15773

Summary: Currency symbol left justified, while amount right justified in a cell (accounting notation).
Product: Calc Reporter: ggabriel <gablistas>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: antonin.guy, apenning, cno, dave_hudson, fippu, frank.loehmann, ilanambeer+apacheopenoffice, issues, lars_o_hansen, lplanken, matthijs, pagalmes.lists, weigel, wulfknight
Version: OOo 1.1 Beta2   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 15522    
Description Flags
For your interest how it simply works by using Apple's Numbers
screenshot of imported Excel accounting format none

Description ggabriel 2003-06-18 17:19:21 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.
Gabriel Gazzán
Comment 1 oc 2003-06-19 13:16:01 UTC
Hi Bettina,
this is important for improving the xls-filter (and an often mentioned
missing feature)
Comment 2 bettina.haberer 2003-12-04 18:29:58 UTC
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged
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.
Comment 3 niklas.nebel 2004-01-26 11:26:43 UTC
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.
Comment 4 niklas.nebel 2004-01-30 10:24:21 UTC
*** Issue 16451 has been marked as a duplicate of this issue. ***
Comment 5 frank 2004-10-29 08:47:33 UTC
*** Issue 36318 has been marked as a duplicate of this issue. ***
Comment 6 frank 2004-11-19 10:23:15 UTC
*** Issue 36943 has been marked as a duplicate of this issue. ***
Comment 7 tracey002 2005-11-16 14:59:49 UTC
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
Comment 8 frank 2006-02-17 11:47:02 UTC
*** Issue 57758 has been marked as a duplicate of this issue. ***
Comment 9 villeroy 2006-04-29 12:28:11 UTC
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."
Comment 10 airon 2006-04-29 14:04:02 UTC
I also would like to see this feature added in next version of OO Calc. Pls we
need it.
Comment 11 rsteinmetz70112 2006-06-26 23:05:36 UTC
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.
Comment 12 grsingleton 2006-06-26 23:23:33 UTC
Change version so it isn't lost
Comment 13 frank 2006-06-27 08:05:54 UTC
Please don't change the Version fiels as it indicates the first occurence of the
Comment 14 grsingleton 2006-06-27 12:13:23 UTC
okay. Just didn't want it to disappear like so many others.
Comment 15 ggabriel 2006-06-27 16:50:18 UTC
Could you please revise the "Target Milestone" field to at least "OOo 2.x"?  
OOo LAter, makes it seem it has no importance. 
Comment 16 mbrio 2006-12-15 17:14:38 UTC
oh... I see that isn't implemented yet... I think it's a very usefull option...
please implement it!
Comment 17 fippu 2007-02-12 20:07:10 UTC
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
Comment 18 rmax 2007-05-27 18:56:04 UTC
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
Comment 19 apenning 2007-06-01 21:05:14 UTC
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!

Comment 20 exjrsygrl 2007-06-02 14:46:10 UTC
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.  
Comment 21 bettina.haberer 2007-09-26 15:56:56 UTC
Hi Niklas, these RFEs are in your ownership.
Comment 22 exjrsygrl 2007-09-27 15:43:21 UTC
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.
Comment 23 holson 2007-10-05 13:34:00 UTC
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
Comment 24 cno 2007-10-31 09:36:13 UTC
Added (accounting notation) to the summary
Comment 25 huw 2008-04-09 09:53:28 UTC

Issue 15756 - enhancement request for pre-defined number format for displaying 
negative numbers in brackets.
Comment 26 nismoc32 2008-05-07 14:21:42 UTC
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.
Comment 27 iooo 2008-05-22 06:50:33 UTC
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?
Comment 28 frank.loehmann 2008-05-22 09:29:38 UTC
This issue is important and listed on the quarterly review for Calc:
Therefore adjusting target to 3.x.
Comment 29 frank.loehmann 2008-05-22 09:33:13 UTC
This issue is important and listed on the quarterly review for Calc:
Comment 30 billrobo 2008-05-29 01:01:30 UTC
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.

Thanks for a job well done thus far, and keep up the excellent work :)
Comment 31 juniorpolegato 2008-10-28 22:34:14 UTC
Hi! Does anyone know if this issue have been solved in 3.0? Does anyone have a
workaround for this issue? Thanks. Junior Polegato.
Comment 32 holson 2008-10-29 00:30:50 UTC
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... :-(
Comment 33 billrobo 2008-10-29 01:17:03 UTC
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).
Comment 34 apenning 2009-08-10 12:19:23 UTC
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

Also, 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 ;)
Comment 35 Stefan Weigel 2009-12-23 16:08:18 UTC
*** Issue 107770 has been marked as a duplicate of this issue. ***
Comment 36 Regina Henschel 2010-04-11 12:36:45 UTC
*** Issue 110774 has been marked as a duplicate of this issue. ***
Comment 37 antonad 2010-04-12 04:28:46 UTC
Any good news for this issue???
Comment 38 suzukirf600 2010-06-18 09:13:48 UTC
very useful format
Comment 39 mjneedles 2010-06-18 16:13:34 UTC
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.
Comment 40 exjrsygrl 2010-06-18 19:05:13 UTC
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.
Comment 41 iooo 2010-06-19 02:40:52 UTC
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
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 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.
Comment 42 vitriol 2010-06-21 10:10:40 UTC
This would be a useful feature.
Comment 43 j_porter 2010-07-01 23:26:09 UTC
This is ridiculous. SEVEN YEARS later, and not even any initial move to fix this?
Comment 44 cno 2010-07-02 07:27:33 UTC
@  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?
Comment 45 j_porter 2010-07-02 13:00:17 UTC
@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, 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.
Comment 46 niklas.nebel 2010-07-02 13:31:07 UTC
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.
Comment 47 cno 2010-07-02 14:55:40 UTC
@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
community can improve. Reality is tough though, so contributions beside
suggestions are welcome too.
Thus: thanks for your reply :-)
Comment 48 kitsune22 2010-07-31 13:39:41 UTC
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!
Comment 49 wulfknight 2012-12-30 05:29:12 UTC
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! :)
Comment 50 wulfknight 2012-12-30 05:34:30 UTC
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!
Comment 51 dave_hudson 2014-02-01 08:14:39 UTC
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.##"))
Comment 52 dave_hudson 2014-02-01 08:26:52 UTC
I forgot to add, this needs to be used with a monospace font such as Courier New, unfortunately
Comment 53 fippu 2014-02-01 11:41:23 UTC
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.
Comment 54 apenning 2014-08-13 12:12:48 UTC
Created attachment 83831 [details]
screenshot of imported Excel accounting format
Comment 55 apenning 2014-08-13 12:14:30 UTC
I just added a screenshot of LibreOffice that comes with Ubuntu.
It now seems to preserve accounting formatting as imported from Excel 2010.
Comment 56 ilana 2016-08-06 16:44:45 UTC
I may actually purchase MS Excel after reading this thread. This is a very necessary feature.
Comment 57 cno 2016-08-06 20:24:51 UTC
(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

Comment 58 Marcus 2017-05-20 11:01:02 UTC
Reset assigne to the default "".
Comment 59 oooforum (fr) 2017-10-17 16:23:07 UTC
*** Issue 16819 has been marked as a duplicate of this issue. ***
Comment 60 GuyAntonin 2017-10-17 17:05:10 UTC
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