+ Reply to Thread
Results 1 to 13 of 13

Ordering chronologically in a pivot table

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Ordering chronologically in a pivot table

    I have dates, and a number corresponding on those dates. I want to try and find the average number for each quarter, so I created a pivot table. My problem is that the pivot table is ordered by the first digit, instead of chronologically and I cant seem to fix this. The sort option doesn't help.
    Screenshot: http://i.imgur.com/duoNai7.png


    Edit:
    In short what I want to accomplish:
    1. Order dates chronologically in the pivot table
    2. Group dates by quarter
    3. Find the average rate for each quarter

    Using this excel sheet:
    http://ubuntuone.com/3WinjDvrwyumHUeZm2c5Cs
    Last edited by Falc; 07-09-2013 at 06:30 PM.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Ordering chronologically in a pivot table

    Hi,

    Not quite sure without seeing your actual sheet, but that first entry (29.02.1988) is inconsitent with the rest and I would guess is therefore in text, not date, format, which may cause you issues.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    Hi
    Thanks for your reply. Yes I noticed that, I corrected it, but excel still refused to sort it chronologically.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Ordering chronologically in a pivot table

    Unfortunately without seeing an actual Excel sheet it's going to be quite difficult to help you.

    Regards

  5. #5
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    Oh sure, i've uploaded it to google docs
    https://docs.google.com/file/d/0B7vu...it?usp=sharing

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Ordering chronologically in a pivot table

    I must be doing something wrong but all I see is 4 pictures in there. Can you direct me to the actual spreadsheet itself?

    Sorry if I've missed it.

    Regards

  7. #7
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    Strange, clicking that link should bring up the spreadsheet with the option to download as follows: http://i.imgur.com/ORXaWvz.png

    Anyway I've uploaded it to ubuntu one as well:
    http://ubuntuone.com/3WinjDvrwyumHUeZm2c5Cs
    Last edited by Falc; 07-09-2013 at 06:21 PM.

  8. #8
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    Edit:
    In short what I want to accomplish:
    1. Order dates chronologically in the pivot table
    2. Group dates by quarter
    3. Find the average rate for each quarter

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Ordering chronologically in a pivot table

    Hi,

    The second hyperlink does not open either.

    Regards

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Ordering chronologically in a pivot table

    Just do a find and replace on that column, change . to / and then pivot, all your dates are text, not dates.

  11. #11
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    I've got the dates formatted as dates now, but I get a divide by zero error:
    http://i.imgur.com/uJ7T4ih.png
    Attached Files Attached Files

  12. #12
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Ordering chronologically in a pivot table

    it's the Discount rate, not showing as number - you have spaces in there!

    =TRIM(SUBSTITUTE(E3,CHAR(160),CHAR(32)))*1


    This will remove the Unicode character for space and the 'usual' one. The *1 converts the result to a number. Update your pivot table to be looking at this new column, and your all good.
    Attached Files Attached Files
    Last edited by GeneralDisarray; 07-25-2013 at 01:39 PM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  13. #13
    Registered User
    Join Date
    07-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ordering chronologically in a pivot table

    Thanks for this! Rep added

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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