Tuesday, March 31, 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

    • Sat 28-Mar-15
      Hi Vsiqueira, I used to get a similar problem if I switched my android device off and on again.
      Joel L.  
    • Fri 27-Mar-15
      hi, im getting an erro, in the method MyLocation.getL ocation(), return null, how i can resolve ...
      vsiqueira  
    • Wed 25-Mar-15
      Hi Rishikesh, I use a database on a webserver with all the latitudes and longitudes of the points of ...
      Joel L.  
    • Tue 24-Mar-15
      At the starting asa I run the app. I want to show list of garages available near by my current location.
      Rishikesh
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Mon 23-Mar-15
      Worked like a charm first time! :lol: Getting bored of the spam comments. This should stop them. :P
      Graham