Wednesday, April 16, 2014
Text Size

MS Excel - Sort pivottable column headings by date

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.

The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).

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!

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





Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Fri 28-Mar-14
      You are a rockstar mate! thanks. Followed the steps listed and it worked! If only all tutorials were ...
  • K2 Items disappear

    • Thu 03-Apr-14
      The fix works great, but the problem is occurring a couple of times a day. Any idea how to fix ...
      Larry C.