Control format of currency

English
#1

I would like to format a column of calc cells with this USD currency format:
$, left aligned … amount, with ‘,’ separator for thousands, right aligned … no decimal place (and no digits indicating 10s and 1s of cents) … negative values in red.

  1. How do I do that please? The GUI controls do not support this, and copying and pasting formats that look like this doesn’t work.
  2. Is there documentation for the syntax and semantics of the currency formatting that explains this?

Thanks
Happy holidays
Arthur

#2

Cells in Currency Format

Can that help?

1 Like
#5

Also help on Number Format Codes is useful. Cheers, Al

#3

See cell A1.
Currency.ods (8.1 KB)

1 Like
#4

Yes, but that needs clarification.
Asterisk (*) is used to repeat characters. The character immediately following an asterisk will be repeated to fill remaining space in a cell.

#6

Thanks everyone!

I end up with this code: [$$-409]* #,###;[RED][$$-409]* #,###;-
I know it’s not exactly what I asked for.

Parts make sense to me, and parts don’t.

  1. 3 sections separated by ‘;’, which format positive, negative and 0 values respectively.
  2. “[$$-409]” in the positive and negative sections, which means “$”, somehow.
  3. "* " after “[$$-409]” in the positive and negative sections, which means that the space following the “*” will fill the cell between “$” and the number, thereby forcing the $ aligned left. (“To fill free space with a given character, use an asterisk (*) followed by this character.”)
  4. “#,###” to indicate comma as a thousands separator, rounded to the closest integer, and no digits to the right of the decimal point. The difference between “#,###” and “#,##0” isn’t clear to me.
  5. “-” in the zero section, a literal.

Arthur

#7

For [$$-409] a [$...-...] denotes a currency symbol of a locale, the second $ here is the dollar currency symbol, the 409 is the locale ID (MS-LCID) for English-USA. The British Pound in English-UK locale is [$£-809] and the Euro in a German-Germany locale is [$€-407].

The difference between #,### and #,##0 is that a 0 is displayed with the latter but not with the former, which displays an empty string for 0 (which doesn’t matter in your case because you defined an explicit third subformat - for 0).

Btw, if with your number format numbers were displayed/printed in monochrome then negative numbers would be undistinguishable from positive numbers as the only difference is the red colour.

2 Likes
#8

In accounting, the tradition of writing negative numbers in red has been around for hundreds of years. Perhaps (there are other points of view) this reduces the likelihood of document forgery.
Displays come and go, but accounting stays. :slightly_smiling_face:

1 Like
#9

Nitpick: professional accounting uses two columns, credit and debit, and a third column with running total and colours for eye candy.

But professional accounting also doesn’t use spreadsheets…

#10

At least one uses (that’s me). :slightly_smiling_face:
Spreadsheets are widely used to generate various reports with a complex structure. And, of course, corporate systems are the source of data.

#11

The actual accounting is done by these systems and your spreadsheet is just a reporting outlet. Whatever errors and mishaps you do on the sheet has no efffect on the actual business data.

#12

Oh sure.
But I’m not going to make mistakes. :confused:

However, some calculation errors in spreadsheets do come at a cost.

#13

Well, this error is about wrong analysis (lost references).
Wrong Tools Cost Lives describes what happened recently with real world database data stored in sheets, edited and saved by uneducated users.

#14

Thank you, erAck.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.