+ Reply to Thread
Results 1 to 4 of 4

Chronological Ordering Of Time Periods In Pivot Table

  1. #1
    Registered User
    Join Date
    05-24-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2

    Chronological Ordering Of Time Periods In Pivot Table

    Any help on the following question is appreciated!

    I have a report that I'll be updating each month. After I run the report I'll refresh a Pivot Table and it will update with the latest data. I have 104 one week time periods that I would like to have sequentially in the row of the Pivot Table. Each month when I update the data the oldest 4 one week time periods will drop off and the latest 4 one week time periods will be added. So, the Pivot Table will always have the latest 104 one week time periods in it.

    In the report that I'm setting up the weeks come out of the report saying "Week Ending 01-01-17" for example. When I refresh the Pivot Table the order it puts the time periods is not sequential. Instead, the order starts with "Week Ending 01-01-17". Then the second time period listed is "Week Ending 01-03-16." The third one listed is "Week Ending 01-08-17". And it continues from there. Is there a way to have all the time periods order sequentially so that each month when the new data comes in it will automatically order the information sequentially in the Pivot Table when I hit refresh? Is it not ordering it properly right now because the fact that it has "Week Ending" at the beginning of the time period it's making it so the dates are being read as text and not actual dates?

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Chronological Ordering Of Time Periods In Pivot Table

    Not knowing what you have to work with, I will assume that the report you run is "being nice" and tacking on the "Week Ending" to make it pretty for human eyes to read. If this is all you have, I suggest you have a helper column on the source data =SUBSTITUTE(A2,"Week Ending","") and then wrap that in a DateValue. =DateValue(SUBSTITUTE(A2,"Week Ending","")). Use this in your pivot table to sort on.

    If you copy / paste your data into an Excel Table and use that as the pivot table source, the Excel Table will remember the formula and copy it down for you automatically.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-24-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2

    Re: Chronological Ordering Of Time Periods In Pivot Table

    Thank you!!! I followed exactly what you recommended (not knowing 100% what it all meant) and it worked!!!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Chronological Ordering Of Time Periods In Pivot Table

    I glad it worked out. If you are satisfied, please mark the thread as Solved.

    Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Ordering Data Table under Pivot Chart
    By namharb616 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-15-2016, 01:47 AM
  2. Chronological ordering of dates in Powerpivot/ Power View
    By MasterElaichi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-08-2015, 07:50 PM
  3. Correcting Time Periods for Pivot tables
    By tcfpny in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2015, 06:15 PM
  4. [SOLVED] Ordering states in a pivot table so that United States appears at the bottom
    By cmcgath in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2014, 05:29 PM
  5. Replies: 0
    Last Post: 03-17-2014, 08:41 AM
  6. [SOLVED] Ordering chronologically in a pivot table
    By Falc in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 07-30-2013, 09:11 AM
  7. Chronological Time in Column
    By Landsurveyor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-02-2009, 03:39 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