+ Reply to Thread
Results 1 to 2 of 2

Pivot table grouping by date. Formatting inconsistency

  1. #1
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac

    Pivot table grouping by date. Formatting inconsistency


    Can anyone explain what might be happening here and whether it's a bug or what I'm misunderstanding.

    It's a given that formatting doesn't alter underlying values in all areas of Excel, merely changing the way cells look. But in the attached I show two pivot tables using exactly the same data with the sole exception that the first one has the date numbers formatted as numbers whereas the 2nd PT has the date numbers formatted as dates.

    With the 2nd PT it will accept the Group by month functionality whereas the 1st PT won't.

    On the face of it PTs seem to take more notice of the way a number is formatted when it comes to grouping rather than treating the number as a date which it can group.

    Not a big deal but it came up in a thread I was answering the other day and had me puzzled.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus

    Re: Pivot table grouping by date. Formatting inconsistency

    I investigated and if I re-select the range to include the timestamp column and force a refresh on the data source (and cache) it allows me to group by number interval. I wonder if has something to do with the pivot table cache? Once you connect to the source, the pivot writes the data and types up in cache and maybe that causes it to be a bit more "sticky".

    Just a thought!

+ 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. [SOLVED] Formatting the date column for grouping in a pivot table
    By highlystrung in forum Excel General
    Replies: 7
    Last Post: 05-09-2012, 01:28 PM
  2. Pivot Table grouping by date
    By sm6132 in forum Excel General
    Replies: 4
    Last Post: 05-04-2010, 12:15 PM
  3. Pivot Table data inconsistency
    By Prium in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-07-2008, 07:35 PM
  4. Pivot Table Date Grouping?
    By DangerMouse in forum Excel General
    Replies: 5
    Last Post: 01-18-2007, 12:24 PM
    By Tufail in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2006, 12:25 AM


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