DESKTOP EDITION CLOUD EDITION SERVER EDITION GUIDES FORUM

Export to excel, negative numbers as text


#1

It seems odd that the last post on this subject was Mar '16 so maybe others are not having this problem or maybe no one has negative numbers in their report but we are not all that lucky so I am revisiting this topic now.

Using
-latest Manager desktop v. 17.7.52
-excel 2007 (old but still works for me)
-Windows 10 Pro

Reports tested
-Profit & Loss
-Balance Sheet
-Statement of Changes in Equity

In all of these report a negative number is prefixed with "− " as in “− 10”. This results in a text field which is very difficult to convert to numeric in excel. So what is the problem?

In excel a negative number starts with
"-" whereas, as noted above in a report copied with the Export feature a negative number starts with
"− " if you look closely you will see these are not the same.

The "− " generated by Manager has two problems. 1. There should be no space between the dash and the first digit. 2. the dash (notice I do use a term related to negative numbers) is not the ASCII symbol for a negative amount which is #45. I could not discouver the ASCII code for Manager’s dash but it is not #45.

Workaround, in case any reader is interested.
In excel do an in-cell edit and copy the "− " (not the quotes of course), and then do a Find and Replace finding "− " and replacing with “-”. This will make all of the negative as text amounts numbers that excel can use. Please note you can’t type in the Find part as, on my key board at least, the dash does not exist.

Solution
Correct the programming as I seriously doubt any spread sheet or accounting program deviates from the ASCII norm for a negative sign.

If I am dead wrong with all of this please let me know.
Thanks


#2

You are not wrong. This is a bug; I will elevate it to Bugs status.

First, let’s clarify terminology. You refer to this problem as occurring in reports. It doesn’t, because reports show negative numbers enclosed within parentheses. The problem occurs in exported TSV files, where everything is handled as text.

Second, the “-” symbol normally used as a minus sign is a hyphen, represented in HTML as &#45. The “−” is an en-dash, used in typesetting to define ranges of numbers, and is generally preceded and followed by spaces. For example, “1985 − 2015.” It is represented as &#8211.

Some text editors, including Microsoft Word, will, by default, convert a hyphen preceded and followed by spaces into an en-dash. (For those with too little to do, there is also an even longer em-dash, used to set off parenthetical thoughts or indicate a break in narration. Word converts a double hyphen into an m-dash. The em-dash is represented by &#8212.)

Why this happens in Manager, I don’t know. Clearly the math is performed accurately. The program knows to present negative numbers in parentheses. But upon export, the en-dash (with space) gets inserted instead of the hyphen/minus.


#3

Obvious to me but not to others I was referring to reports after exporting to excel. Thanks for the reply.


#4

Fixed in the latest version (17.7.84)


#5

The problem seems to fixed in Profit & Loss Statement and Balance Sheet. But it persists in:

  • Statement of Changes in Equity
  • Tax Summary
  • Tax Reconciliation
  • Tax Transactions
  • Inventory Value Movement
  • Inventory Profit Margin
  • Capital Accounts Summary
  • Billable Time report

It may happen in a few other reports where none of my companies have negative quantities. These include:

  • Expense Claims Summary
  • All the payslip reports

I’m moving this back to Bugs.


#6

Should be fixed everywhere in version 17.7.85


#7

Fixing this helps a lot. Thanks