+ Reply to Thread
Results 1 to 7 of 7

Pivot table does not sort grouped dates properly

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

    Pivot table does not sort grouped dates properly

    Hi Everyone

    I have looked all over the net for this but cannot seem to find an answer. I have made a very simple pivot for my boss to see the number of referrals per staff. The raw data has a date column. We dont need the actual file since I have noticed this problem with any other data set I have tried since.

    When I group dates by days and press sort from old to new or new to old, then the range of dates does not work properly anymore.

    Capture.PNG

    What annoys me the most is not being able to find an answer anywhere

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Pivot table does not sort grouped dates properly

    I'm not a pivot table guru, so can't advise directly, but maybe this will help?
    https://answers.microsoft.com/en-us/...c-96caee958144
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,464

    Re: Pivot table does not sort grouped dates properly

    Using Ingeborg's idea of weeknumbers, I have amended your table to include a weeknumber and a year and then pivoted on the year and weeknumber. I have then hidden the year and weeknumber columns.
    Attached Files Attached Files

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Pivot table does not sort grouped dates properly

    When you create the pivot table and add the date field. Group it in weeks, but don't sort it.

  5. #5
    Registered User
    Join Date
    10-02-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

    Re: Pivot table does not sort grouped dates properly

    Thanks for your replies all. It looks like the limitation is with excel and I personally liked the method suggested by Alan the most. Thanks again, I have added reputation to all for your willingness to help.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,464

    Re: Pivot table does not sort grouped dates properly

    Thanks for the rep

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Pivot table does not sort grouped dates properly

    Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 05-03-2017, 02:12 PM
  2. [SOLVED] Sort dates in Pivot table
    By Wskip49 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-17-2017, 08:42 AM
  3. Pivot table does not sort text properly
    By brunoalmeida in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-11-2017, 10:44 PM
  4. Replies: 2
    Last Post: 03-20-2015, 01:57 PM
  5. Date Sort in Pivot Table - Not working properly
    By claudiocustodio in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-12-2013, 04:30 PM
  6. [SOLVED] Pivot Table - Grouped Dates - Format
    By PlanoMax in forum Excel General
    Replies: 3
    Last Post: 04-29-2006, 04:40 PM
  7. How to sort dates in Pivot table?
    By wmw in forum Excel General
    Replies: 2
    Last Post: 02-06-2005, 01:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1