Bug 45780 - Accounting notation FORMATTING
Summary: Accounting notation FORMATTING
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: high enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 48810 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-02-08 07:00 UTC by PhilC2K
Modified: 2014-02-04 15:54 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description PhilC2K 2012-02-08 07:00:39 UTC
Brief: Add Accounting number format to left justify the currency symbol and right justify the decimal number within a cell and use "()" for negative numbers.

This is a long standing bug/deficiency from the early OOo days.  This does not get many votes because the people that _REQUIRE_ this are not the population of people who report bugs, they are the _ADOPTERS_, the very people we are trying to target.  All they know, or want to know, is that "it just doesn't work".

This needs to be elevated and dealt with.  This may seem trifling to us, but this goes straight to the heart of our credibility to those we are trying to get to adopt LO in particular and FOSS in general: "If they can't even do this, what else must be wrong".  They don't want to know, need to know or should have to know the inner workings of the FOSS development process.

OPTION1:  A format notation functionally similar to the M$ Excel "*?" which expands whatever the "?" character is to fill the cell.  For example:

[>0]$* #,##0.00;[<0][RED]$* (#,##0.00);$* -##;@

OPTION 2:  A notation to left, center or right justify the following characters within the cell.  For example:

[>0][LJ]$[RJ]#,##0.00;[<0][RED][LJ]$[RJ](#,##0.00);[LJ]$[RJ]-##;@

This will give as a significant extra general formatting tool as well as solve this particular problem.
Comment 1 Matt Johnson 2012-04-05 15:29:36 UTC
I just noticed this problem as well as I attempt to move my organization toward more FOSS solutions.  I'm grabbing the git repository right now and going to see if I can fiddle with the code a bit and help out.  I'm new to this project, so I'm not sure how to make sure I get notified of any changes to it, but I clicked the 'Add me to CC'  Please let me know if there's more to that part of it.
Comment 2 Lionel Dricot 2012-04-24 02:04:24 UTC
Could you confirm that, in order to fix this bug, there should be a new "accounting" format (adding a button next to the "currency" format) which have the following properties:

a) alligning the currency symbol on the left
b) replacing 0 value by a dash.
c) putting negative numbers between parenthesis.

I'm not sure about the color.
Comment 3 Andrea Pescetti 2012-05-08 14:53:22 UTC
For the record: the corresponding OpenOffice issue, that has some more background information, is https://issues.apache.org/ooo/show_bug.cgi?id=15773
Comment 4 Roman Eisele 2012-05-26 09:25:33 UTC
This is a feature/enhancement request, therefore changing the Importance field accordingly.
Comment 5 PhilC2K 2012-05-27 06:42:21 UTC
This is NOT some mundane enhancement, this is a major DEFICIENCY since the days of OOo 1.1 Beta2 of basic functionality!  This kind of thing goes straight to the heart of our acceptance and credibility in the eyes of the adopters.  Imagine how serious we would be taken if you could not full-justify text in Writer.
Comment 6 Michael Adams 2013-02-12 22:14:50 UTC
I'm thinking this need to be marked "RESOLVED - DUPLICATE" referencing this bug:
https://bugs.freedesktop.org/show_bug.cgi?id=34943

I'd have done so, except I'd kind of like a second opinion from an actual LO dev...
Comment 7 PhilC2K 2013-02-14 03:47:30 UTC
I see that they implemented the M$ method of repeating fill characters.  The resulting LO format for the financial accounting format wold be:
[$$-409]* #,##0.00;[RED][$$-409]* (#,##0.00);[$$-409]* -##;@

That solves that problem, BUT that format must be added to the list of available Currency formats category permanently.  Another option is the "Add" button add the format to the list so it is always there.

Until they fix the list of formats, this should remain open.
Comment 8 Michael Adams 2013-02-16 16:07:56 UTC
(In reply to comment #7)
> I see that they implemented the M$ method of repeating fill characters.  The
> resulting LO format for the financial accounting format wold be:
> [$$-409]* #,##0.00;[RED][$$-409]* (#,##0.00);[$$-409]* -##;@
......
> Until they fix the list of formats, this should remain open.

Agreed.

It would also be nice if the "[$nnn-xxx]" specifier could be *extended* so that just "[$#-#]" (typed typed like that) would mean:  Just give me the current currency symbol in the current locale, /whatever/ it is.  Replacing the first "#" with a symbol would mean: Give me the current currency symbol in the current locale, if none, use what's listed in place of the first "#".

My reasoning for the use of "#" was that, in general numeric formatting, "#" is: if there is a number for this "spot" use it, else omit it. That is, if we aren't in a defined locale (??) or the locale doesn't specify a currency symbol, we omit the symbol.

My reasoning for the introduction of such a "short-hand" is simple laziness!  One less item to have to look up or remember (what *is* the locale number of "de_ch" in LO again?).

Of course, my ideas and reasoning are hardly definitive :).

All my financial reporting sheets and most of those companies/persons I exchange such data with are all in the same locale (en_us).  I think that would be a nice shorthand for those of us that do not (currently) require multi-locale support, while supporting a graceful way to "place-hold" the specifier for when/if that requirement *does* change.
Comment 9 Michael Adams 2013-02-16 16:28:15 UTC
(In reply to comment #8)
> (In reply to comment #7)
> > I see that they implemented the M$ method of repeating fill characters.  The
> > resulting LO format for the financial accounting format wold be:
> > [$$-409]* #,##0.00;[RED][$$-409]* (#,##0.00);[$$-409]* -##;@
> ......
> > Until they fix the list of formats, this should remain open.
> 
> Agreed.
> 
> It would also be nice if the "[$nnn-xxx]" specifier could be *extended* so
.......... 
> Of course, my ideas and reasoning are hardly definitive :).

Also, once I've thought more, why couldn't we get rid of the (to me) silly locale number code idea?

Isn't "en_us" *better* (more self documenting) than "409"?

Wouldn't "[$#-en_us]* #,##0.00;[RED][$#-en_us]* (#,##0.00);[$#-en_us]* -##;@", to roll-up my "complete" idea as a concrete example, be more understandable?

After all, computers and programs exist to assist with the repetitive and mundane so why shouldn't Calc figure out what the locale number for "en_us" (e.g.) is and just "do the right thing"?
Comment 10 PhilC2K 2013-04-15 04:52:04 UTC
At the very least, add this to the Currency list of formats, or create an Accounting category:

[$$-409]* #,##0.00_);[RED][$$-409]* (#,##0.00);[$$-409]* -_)
Comment 11 ign_christian 2013-10-09 13:36:27 UTC
*** Bug 48810 has been marked as a duplicate of this bug. ***
Comment 12 Robinson Tryon (qubit) 2014-02-04 15:54:08 UTC
(In reply to comment #7)
> That solves that problem, BUT that format must be added to the list of
> available Currency formats category permanently.  Another option is the
> "Add" button add the format to the list so it is always there.
> 
> Until they fix the list of formats, this should remain open.

This notation is possible, and bug 63536 is now labeled as an enhancement and has status: NEW, so resolving this bug as WORKSFORME.