Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Detroit, Michigan, USA
    Thanked 0 Times in 0 Posts

    Pivot Tables with Dates (2000)

    I have a large set of data, from which I am creating pivot tables. It works great except for formatting date fields...seems I want to format the date column to be MMM-YY when I format it and then use the column in a pivot table or pivot chart, the pivot shows MM-dd-yy format, with way too many permutations in my chart. I just want to show MMM-YY in my pivots and use to select on...I must be missing something? Any suggestions. Thanks much.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Pivot Tables with Dates (2000)

    A format is just the way data are displayed; it doesn't alter the underlying data. When Excel constructs the pivot table, it uses the underlying data, not the formatted data.

    But you can group the dates by month after creating the pivot table:
    Select one of the dates, then select Data | Outline and Group | Group (you can also right-click the date)
    Make sure that Months is selected in the By box, then click OK.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables with Dates (2000)

    In addition to grouping in the pivot table, you can add additional columns to your data:
    For example Year, Month/Year (I use day1). This allows adding the year or the month/year to the pagefield if you want to get only a subset of the data
    The downside is 2 additional columns in data whihc I do not consider to ghastly. I tend to use this technique also with autofilter to allow slecting by the year and month/year directly without tinkering with a custom selection in the date field.


Posting Permissions

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