[Solved] Number format - accounting

Discuss the spreadsheet application
Post Reply
egwkansascity
Posts: 2
Joined: Sun Nov 16, 2008 5:30 pm

[Solved] Number format - accounting

Post by egwkansascity »

I am embarressed to say I have been unable to find the comparible function in calc to excel that allows me to simply select a number format for accounting work that provides for () around negative numbers and the ability to designate no decimel places and apply this format to the whole worksheet as the number format for the sheet. I would prefer that every new sheet open with this as the default format.

Sorry to interject such a simple concept need - maybe I have simply missed it - truly new to calc but wanting to use it for all of my spreadsheet needs.

Thanks - Eldon
Last edited by Hagar Delest on Sun Nov 16, 2008 10:38 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 2.3.X on Ms Windows XP
User avatar
bobban
Volunteer
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: number format - accounting

Post by bobban »

Hi Eldon,

A 'true' accounting format is not possible I believe, because it is not possible to have numbers on the right and a currency symbol on the left in one cell. I am not experienced with accounting but I have seen a number of people inquire about this . The most commonly proposed workaround used to have a very narrow column alongside containing just the currency symbol. When you are looking at the preset formats, you can manually edit the format code at the bottom. If you press the help button, there is a good explanation of all the various options, and it is not as hard as it seems to create unique formats, especially when you can base them on the existing precepts.In regards to your situation, here is a format code based on the "positive and negative numbers" section of the help manual:

(#,###.00);[RED]-#,###.00;#,###.00

The first section is related to positive numbers, and hence contains the brackets, the second section is related to negative numbers, and you can remove the "red" if you want, and the last section is for numbers equal to zero.

To make this format a permanent feature of your Calc, you must edit the default template.

If this question is entered, please edit the original post thread title by putting [Solved] in it.
OOo 3.1.1 on Ms Windows XP
egwkansascity
Posts: 2
Joined: Sun Nov 16, 2008 5:30 pm

Re: number format - accounting

Post by egwkansascity »

Bobban - thanks for the help.

The format string you gave me did not get me a negative number in () but - using this concept - here is what I did to solve my problem and then created the default temp file to use going forward.

1) I opened an existing excel file with negative () numbers in it - I opened it with oorg and found that the set up string for this format then existed in the number format in oorg file.
2) I copied the string and opened a new oorg worksheet and then in "cells-format-numbers-user defined", I pasted the format string that I copied from excel.
3) That gave me a choice then in the new worksheet to selected that format for the worksheet and I made it global to the sheet.
4) I then went through your instructions for saving the worksheet as a new template and then I also went through the steps of making it the default worksheet when I opened a new worksheet.
5) It seems to work. WOW!!

Here is the formula I lifted from the from the oorg/excel file and pasted in oorg template for "cell-numbers-user defined".
[>0]#,##0 ;[<0]" ("#,##0);" - ";@

Thanks for the help - problem solved for now.

Eldon
OOo 2.3.X on Ms Windows XP
User avatar
bobban
Volunteer
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: [Solved] Number format - accounting

Post by bobban »

The format string you gave me did not get me a negative number in () but - using this concept - here is what I did to solve my problem and then created the default temp file to use going forward.
You're right, I hurried my response and misread that the brackets should be around negative numbers, not positive. I should have suggested:

#,###;(#,###);0

or

#,###;(#,###);" - " , if you want " - " instead of zero.

Note: You're solution is very similar in fact. It just uses 'conditional' operators (>0, <0 etc), but when there are just three terms separated semi colons Calc interprets these as relating to positive, negative, zero.

Good working getting it sorted.
OOo 3.1.1 on Ms Windows XP
chewybb
Posts: 4
Joined: Sun Jun 30, 2013 6:18 pm

Re: [Solved] Number format - accounting

Post by chewybb »

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.##"))

:D
OpenOffice 3.4.1 on Windows 7
chewybb
Posts: 4
Joined: Sun Jun 30, 2013 6:18 pm

Re: [Solved] Number format - accounting

Post by chewybb »

I forgot to add, this needs to be used with a monospace font such as Courier New, unfortunately.
OpenOffice 3.4.1 on Windows 7
Alex1
Volunteer
Posts: 723
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: [Solved] Number format - accounting

Post by Alex1 »

This means you need an extra cell for each value. You can replace the leading #'s by spaces in an existing format to achieve the same.
AOO 4.1.15 & LO 24.2 on Windows 10
nissley
Posts: 1
Joined: Sun Jan 15, 2017 7:28 am

Re: [Solved] Number format - accounting

Post by nissley »

In LibreOffice I used this format:

#.##0,00 [$€-407] ;-* #.##0,00 [$€-407]

The * makes it possible to get the - to the left and the number to the right.
OpenOffice 2.4 on Ubuntu 9.04
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: [Solved] Number format - accounting

Post by soby »

with the new version of AOO 4.1.3 you can have the same formatting as in LO

[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Number format - accounting

Post by Lupp »

Before posting into an old thread, specifically into one marked [Solved], please check if your concern is compatible with the original post.

Here we have some comments concerning a specific question by 'egwkansascity' posted 2008-11-16,
then a rather unrelated post of 2014-02-01 by 'chewybb' only concerning a comment by 'bobban' from 2008-11-16 without mentioning it,
and now two post from 2017-01-15 without a clear relation to the original question.

(By the way: The format code given by 'nissley' has a wrong space between the currency code and the semicolon after the part for >=0, and
the answer by 'soby' does not much worry about the question.)
Last edited by Lupp on Sun Jan 15, 2017 3:14 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: [Solved] Number format - accounting

Post by soby »

Lupp

sorry about that, will not happen again
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Number format - accounting

Post by Lupp »

@soby.
You needn't apologize. Due to my poor English I can never be sure about unintended implications of my words. I hope you didn't feel offended in any way. I also am well aware of the fact that I posted doubtable answers again and again. (And my post above -just rectified- originally contained a year 20008.)

Thanks to everybody contributing to the forum!
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply