+ Reply to Thread
Results 1 to 4 of 4

problem grouping dates in Pivot chart in Excel 2010

  1. #1
    Registered User
    Join Date
    02-26-2014
    Location
    Detroit MI
    MS-Off Ver
    Excel 2010
    Posts
    2

    problem grouping dates in Pivot chart in Excel 2010

    hi,
    so i'm new to excel 2010, but well familiar with older versions.
    i have date fields that I pull into a pivot chart to sort by. then i right click on the date in the pivot table in order to open the grouping dialog box. this all seems the same as excel 2003.
    but the data range for the grouping is showing as the 1900 serial, not a 'date'. so the grouping option is by default '10', which makes no sense for monthly data.

    i've gone over the change format prior to the grouping, and the date displays as a date in the pivot table.

    not sure where to go from here. need to have the dialog box recognize the data as dates so i can group properly.

    thanks

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: problem grouping dates in Pivot chart in Excel 2010

    HAve you verified that they are "Real" excel dates?....choose an open cell and type the following

    =ISNUMBER(Cell) - click enter

    if it is a real excel date then is should be TRUE....Formatting never changes the data itself, only how we see it....so formatting a date does nothing to actually change a TEXT date to a real excel date...this is usually the first thing I check....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: problem grouping dates in Pivot chart in Excel 2010

    Date data needs to be stored in 'date' format before the pivot cache is created, for date grouping to work correctly.

    If you need to change the actual format of your source data, you'll need to recreate your pivotcache for the pivot table to treat the dates the way you want.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    02-26-2014
    Location
    Detroit MI
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: problem grouping dates in Pivot chart in Excel 2010

    hi,

    judgeh59:
    it came back as TRUE.

    OllyXLS:
    you may be on to something, in the source data it showed as 1900 serial. I selected the column and changed number format to date. it now shows as date in the source data.
    I went to the pivot table and did 'options> change data source'.that's my usual step to incorporate changes into the table cache.
    but it sill shows the 1900 serial in the ranges under grouping.
    Is there something more i need to do to capture the change in data? or something more to change the format?

    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. Grouping Dates in Excel Pivot Table
    By mskennedy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-13-2013, 09:33 AM
  2. Replies: 1
    Last Post: 11-28-2012, 01:17 PM
  3. Replies: 1
    Last Post: 03-22-2012, 07:54 PM
  4. Pivot chart problem in Excel 2010
    By phishneslo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-01-2011, 08:08 PM
  5. Problem formatting dates in pivot chart.
    By greg7468 in forum Excel General
    Replies: 2
    Last Post: 02-07-2008, 08: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