Ask Your Question
1

accounting format in calc [closed]

asked 2013-07-09 20:34:18 +0200

Anderson gravatar image

updated 2013-10-11 20:59:41 +0200

manj_k gravatar image

good afternoon.

I'm having problems with the accounting format I use in ms excel.

The issue of alignment and the numbers stay with "(" before and after already decided. However the issue is pending the cells that have the number "0". In MS Excel they are visually with the standard "-" marked, but in libreoffice such cells are visually with the pattern "# # #".

I'm waiting for a possible solution.

I thank you.

Anderson Melo

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 03:33:27.349233

Comments

I experience the same thing, that accounting format from excel shows as "###" in LO. Change the column width sometime work.

Paijo gravatar imagePaijo ( 2013-07-12 11:52:29 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2013-07-12 11:44:52 +0200

Paijo gravatar image

Try this number format for alternative for "accounting".

(I use 'comma' for decimal and 'dot' for thousand separator):

with 2 digits decimal:

_(#.##0,00_);\(#.##0,00\);"-"_)_);_(@_)

without decimal:

_(#.##0_);\(#.##0\);"-"_)_);_(@_)

edit flag offensive delete link more

Comments

Answer to a prayer!

CStanford gravatar imageCStanford ( 2017-03-15 16:51:52 +0200 )edit
1

answered 2014-01-26 13:54:46 +0200

MrP gravatar image

updated 2014-01-26 14:03:20 +0200

I too have experienced the "###" being displayed for zero values when migrating a document from Excel to Calc when using Excel's "Accounting" format. The strange thing is that seemingly the same format code applied to different cells can result in different output (one with "-", the other with "###") and changing the width of the cell does not appear to help! Bizarre. Anyway, this can be fixed...

The "Format code" consists of up to 3 sections, separated by the semi-colon (;). The 3rd section indicates what is shown when the value is zero. (The first two are the positive and negative variants respectively.)

So, to display a hyphen (-) when the value is zero, you could use a format code like this:

#,##0.00;[RED](#,##0.00);"-"

The above format code includes 2 decimal places, 1 leading zero and thousands separators. Negative values are [RED] and surrounded by parentheses. And finally a single hyphen is shown when the value is zero.

You could pad the hyphen with a couple of spaces on the right to make it more Excel like.

edit flag offensive delete link more
0

answered 2013-07-09 22:03:03 +0200

m.a.riosv gravatar image

updated 2013-07-09 22:03:47 +0200

I think works fine with a format like " € ###.##0,00 ", and in Alignment - Text alignment - Horizontal, Distributed.

image description

But I do not know if it is compatible with excel.

edit flag offensive delete link more
0

answered 2013-07-12 16:56:06 +0200

Anderson gravatar image

alignment has decided .. I need to know is how do the number zero appears as "-", as it is in MS Excel format book

Does anyone know?

God bless you all.

edit flag offensive delete link more

Comments

I would just play around a bit with the format indicated by @mariosv and @Paijo and see how "0" appear.

ROSt52 gravatar imageROSt52 ( 2013-07-13 04:24:52 +0200 )edit

In Number Format Accounting imported from MS Excel file, remove the asterisk for zero value shall solve the problem.

Paijo gravatar imagePaijo ( 2013-07-16 09:11:24 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-07-09 20:34:18 +0200

Seen: 4,334 times

Last updated: Jan 26 '14