This is a quick note to myself so that I never use parentheses in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top.
Why?
The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).
How?
See the following screenshot and note the dates for Monday, Tuesday, Wednesday, and Thursday:
Note how amusingly Microsoft thinks Wednesdays come before Mondays... but what's wrong with this equation? You can tell me that "10" comes before "8" and it does but just to check, let me pad the 8 with the prefixing zero:
Annoyingly "10" still comes before "08" so maybe I'm barking up the wrong tree. If I padded with one more zero so that "008" is before "010":
Great! The columns are displayed in the correct order but who's ever seen a date written as the 008th of April?
What if we didn't use parentheses? Then it wouldn't be evaluated as an expression and perhaps Excel will be forced to treat it as a string:
Another alternative to round brackets, square brackets!
Additional:
I have made this work with the parentheses but only after refreshing ALL DATA in the spreadsheet and reconfiguring which fields go in "Report Filter", "Column Labels", "Row Labels" and sum "Values". But because the next time this happens, I would have to repeat this, I would advise the above steps to remove parentheses.
Category: Excel :: Article: 504
Add comment