Tuesday, September 02, 2014
   
Text Size
Login

MS Excel - Sort pivottable column headings by date

What?
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.

Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Quicklist Weblinks (JQW)

    • Mon 01-Sep-14
      Hi from France Download's link for the J3.1.1 version of JQW is broken ... have a nice day, Thanks ...
      tompouce33
  • Joes Word Cloud (JWC)

    • Sat 23-Aug-14
      Salut Joe, Thank you again for helping me. I had installed V2.2 for Joomla 2.5. Finally, as you ...
      Nathalie  
    • Wed 20-Aug-14
      Salut Nathalie! I could only access the under construction page of your website, so I'm not 100% sure.
      Webmaster  
    • Tue 19-Aug-14
      Hi Joe, Thank you for your prompt reply. Below, an "image" of the cloud on my website : "pour ante ...
      Nathalie  
    • Mon 18-Aug-14
      Hi Nathalie, Thanks for the message. The module does not see Virtuemart text but that sounds like ...
      Webmaster