Monday, March 02, 2015
   
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:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 25-Feb-15
      SOrry ive solved the previous error but having an error context.LOCATIO N_SERVICE);
      lindalee  
    • Wed 25-Feb-15
      Hi im getting an error getLastKnownLocation(provider) can not resolve provider any hekp
      lindalee
  • JDatabase: using the Joomla database with examples

    • Sat 28-Feb-15
      High Praise, Thx Scott! I still use this for myself, nice to know it was of some use to you.
      Joel L.  
    • Fri 27-Feb-15
      Wow, thank you so much, Joomla documents are written for those who can read Joomla documents. :lol: Your ...
      Scott N.  
    • Thu 26-Feb-15
      Clearly represented jfactory querys. This helped me a lot. Thank you very much. :-)
      Oddy