+ Reply to Thread
Results 1 to 3 of 3

Pivot tables not keeping date ranges in order

  1. #1

    Pivot tables not keeping date ranges in order

    Hi all,

    I have a spreadsheet that shows the date that quotes were created. I
    want to create a pivot table that just summarizes the number of quotes
    that were created in a given month-year format.

    First I changed all the "created on" dates to month/1/year format
    (where "1" is constant) instead of month/day/year (where "day" is the
    specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
    created a pivot table off that, but the pivot table doesn't keep the
    months in order.

    I have the "created on" field in the row field of the pivot table and
    the "count of" the quotes in the data field. The pivot table puts the
    "created on" field in some order I can't figure out. It has the order
    as:

    6/1/05
    7/1/05
    8/1/05
    9/1/05
    11/1/05
    12/1/05
    2/1/06
    3/1/06
    5/1/06
    10/1/05
    1/1/06
    4/1/06

    I want it to follow our fiscal year, so I want it to read:

    6/1/05
    7/1/05
    8/1/05
    9/1/05
    10/1/05
    11/1/05
    12/1/05
    1/1/06
    2/1/06
    3/1/06
    4/1/06
    5/1/06

    I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
    as it's not 6/13/05 where the specific date is captured. There are
    far too many quotes for me to get any use out of that level of detail.

    Suggestions?

    Thanks.
    Robert

  2. #2
    Debra Dalgleish
    Guest

    Re: Pivot tables not keeping date ranges in order

    Formatting the dates in the source data won't change the underlying date
    that the pivot table uses. You could create another field in the data,
    where you calculate the fiscal month, e.g. 200601, 200602, and use that
    field in the pivot table.
    Or, group the date field in the pivot table, by year and month. This
    will group by calendar year though, not fiscal year.

    [email protected] wrote:
    > Hi all,
    >
    > I have a spreadsheet that shows the date that quotes were created. I
    > want to create a pivot table that just summarizes the number of quotes
    > that were created in a given month-year format.
    >
    > First I changed all the "created on" dates to month/1/year format
    > (where "1" is constant) instead of month/day/year (where "day" is the
    > specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
    > created a pivot table off that, but the pivot table doesn't keep the
    > months in order.
    >
    > I have the "created on" field in the row field of the pivot table and
    > the "count of" the quotes in the data field. The pivot table puts the
    > "created on" field in some order I can't figure out. It has the order
    > as:
    >
    > 6/1/05
    > 7/1/05
    > 8/1/05
    > 9/1/05
    > 11/1/05
    > 12/1/05
    > 2/1/06
    > 3/1/06
    > 5/1/06
    > 10/1/05
    > 1/1/06
    > 4/1/06
    >
    > I want it to follow our fiscal year, so I want it to read:
    >
    > 6/1/05
    > 7/1/05
    > 8/1/05
    > 9/1/05
    > 10/1/05
    > 11/1/05
    > 12/1/05
    > 1/1/06
    > 2/1/06
    > 3/1/06
    > 4/1/06
    > 5/1/06
    >
    > I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
    > as it's not 6/13/05 where the specific date is captured. There are
    > far too many quotes for me to get any use out of that level of detail.
    >
    > Suggestions?
    >
    > Thanks.
    > Robert



    --
    Debra Dalgleish
    Contextures
    http://www.contextures.com/tiptech.html


  3. #3
    Dave Peterson
    Guest

    Re: Pivot tables not keeping date ranges in order

    If they're really dates, you should be able to double click on that grey box in
    the pivottable that represents the date.

    Then click on the advanced button.
    Choose Ascending in the Autosort options section.

    ======
    By the way, you may have been able to keep the real dates, you could try
    rightlclicking on that date field in the pivottable.

    Choose Group and Show detail
    Group
    By months and by years



    [email protected] wrote:
    >
    > Hi all,
    >
    > I have a spreadsheet that shows the date that quotes were created. I
    > want to create a pivot table that just summarizes the number of quotes
    > that were created in a given month-year format.
    >
    > First I changed all the "created on" dates to month/1/year format
    > (where "1" is constant) instead of month/day/year (where "day" is the
    > specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
    > created a pivot table off that, but the pivot table doesn't keep the
    > months in order.
    >
    > I have the "created on" field in the row field of the pivot table and
    > the "count of" the quotes in the data field. The pivot table puts the
    > "created on" field in some order I can't figure out. It has the order
    > as:
    >
    > 6/1/05
    > 7/1/05
    > 8/1/05
    > 9/1/05
    > 11/1/05
    > 12/1/05
    > 2/1/06
    > 3/1/06
    > 5/1/06
    > 10/1/05
    > 1/1/06
    > 4/1/06
    >
    > I want it to follow our fiscal year, so I want it to read:
    >
    > 6/1/05
    > 7/1/05
    > 8/1/05
    > 9/1/05
    > 10/1/05
    > 11/1/05
    > 12/1/05
    > 1/1/06
    > 2/1/06
    > 3/1/06
    > 4/1/06
    > 5/1/06
    >
    > I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
    > as it's not 6/13/05 where the specific date is captured. There are
    > far too many quotes for me to get any use out of that level of detail.
    >
    > Suggestions?
    >
    > Thanks.
    > Robert


    --

    Dave Peterson

+ 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