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.
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!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By highlystrung in forum Excel General
Last Post: 05-09-2012, 01:28 PM
By sm6132 in forum Excel General
Last Post: 05-04-2010, 12:15 PM
By Prium in forum Excel Formulas & Functions
Last Post: 10-07-2008, 07:35 PM
By DangerMouse in forum Excel General
Last Post: 01-18-2007, 12:24 PM
By Tufail in forum Excel Programming / VBA / Macros
Last Post: 06-13-2006, 12:25 AM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1