If only 1 format is given and it doesn't describe a number or date, the "-" sign is missing for negative values #2575

New Issue

2020-09-27T21:21:21Z

snoopyjc commented

2020-09-27 21:21:21 +00:00

(Migrated from github.com)

This is definitely from the "oddity" category! If you have a Number Format that does not have any numeric (or date) parts to it, and only 1 section (no ';'), and a negative number is passed, the result should start with a '-'. Also, the tests for this in oddities.json is incorrect. Please try it in Excel, as I didn't believe it does this either!
Incorrect:

 ["A\"TODO\"", [1, "ATODO"], [-1, "ATODO"], [0, "ATODO"], ["TODO","TODO"]],
 ["\\r", [1, "r"], [-1, "r"], [0, "r"], ["TODO","TODO"]],

Correct:

  ["A\"TODO\"", [1, "ATODO"], [-1, "-ATODO"], [0, "ATODO"], ["TODO","TODO"]],
  ["\\r", [1, "r"], [-1, "-r"], [0, "r"], ["TODO","TODO"]],

This is definitely from the "oddity" category! If you have a Number Format that does not have any numeric (or date) parts to it, and only 1 section (no ';'), and a negative number is passed, the result should start with a '-'. Also, the tests for this in oddities.json is incorrect. Please try it in Excel, as I didn't believe it does this either! Incorrect: ["A\"TODO\"", [1, "ATODO"], [-1, "ATODO"], [0, "ATODO"], ["TODO","TODO"]], ["\\r", [1, "r"], [-1, "r"], [0, "r"], ["TODO","TODO"]], Correct: ["A\"TODO\"", [1, "ATODO"], [-1, "-ATODO"], [0, "ATODO"], ["TODO","TODO"]], ["\\r", [1, "r"], [-1, "-r"], [0, "r"], ["TODO","TODO"]],

SheetJSDev commented

2020-09-27 21:28:10 +00:00

(Migrated from github.com)

Keep the reports coming :)

These were generated from an older version of Excel (I want to say 2011/2013 but some might've been from older versions), we'll go back and check.

Out of curiosity, after setting the format to A"TODO" in Excel, is it automatically rewriting the format as \A"TODO" ? To see this, set the format to A"TODO" in Excel, then try to change the format.

Keep the reports coming :) These were generated from an older version of Excel (I want to say 2011/2013 but some might've been from older versions), we'll go back and check. Out of curiosity, after setting the format to `A"TODO"` in Excel, is it automatically rewriting the format as `\A"TODO"` ? To see this, set the format to `A"TODO"` in Excel, then try to change the format.

snoopyjc commented

2020-09-27 21:34:28 +00:00

(Migrated from github.com)

Yes that is the case as you said. This also applies to the "\r" format and also I'm checking all of the ones from valid.tsv and finding more errors. For example, this same issue occurs on the first few formats listed in that file. (e.g. " Excellent").

snoopyjc commented

2020-09-27 21:40:23 +00:00

(Migrated from github.com)

I'm using Excel 2010.

Sign in to join this conversation.