Pivot table date field won't group

[syswizard]

I've sourced my pivot table from a data table sourced from Oracle.
Even when I change the format of the pivot field of interest to date format, it only prompts using the internal date number ....i.e. 41924, etc.
I tried changing the data table's format for that column as well....still no go.
Excel thinks it's just a number, not a date.
What's the trick here ?

[HansV]

What a weird problem! I'm afraid I don't have access to any Oracle data sources, so I can't test myself, and I can't find anything about this problem...

[Rudi]

Could it be that the field in the Oracle DB is a number data type which is formatted as a date?
Hence Excel displays it as a number and not a date in the Pivot.

Also: Does the pivot field show the number or does the field show the date and the filter drop down shows the number?

[syswizard]

It only shows the number as the filter....not the date formatted value.
This is really something...I wonder if I have to create a separate column in the data source that has a formatted date based on the Oracle date column:
=Format(DateColumn,"mm/dd/yyyy")

I think the Oracle date columns are actually defined as numbers....that's probably the problem...right ?
They are NAMED as dates, but not really created as date columns.
That would make sense.

[Rudi]

IMHO, the pivot takes the raw source data and not the "formatted" data. As you say, it might be worth experimenting with either forcing the design of the "date" field to a date, or as you say, create a new column (as a date defined column) and populate it with a date from the original field.

Hope that helps to resolve it for you...

[syswizard]

I suspect this is an issue between Excel and Oracle. I don't recall having this same problem with MS-Access nor SQL Server.
I think the only fix is to format the date column as a date string.

[syswizard]

Just a bug in Excel: A pivot table RefreshAll does NOT recognize the new format in the data source (It should !!!)
Then it's a huge hassle to re-source the datasource from an external data table....you must select every cell in the data table or you must know the name of the data table.
Much room for improvement here.

[Rudi]

You should maybe consider giving Power Query and/or Power Pivot a try. Integrated into Excel 2013 by default, or available as an add-in for Excel 2010. The Power Query add-in has very capable data cleansing and manipulation tools to clean and structure your source data into a virtual data set from which Power Pivot (or even the standard Pivot Table feature) can connect to. Power Pivot has a lesser array of tools for cleansing and manipulation but would still be more than sufficient to connect with and cope with what you need and then structure a pivot from the virtual; range it too creates.

[syswizard]

I noticed others are including the quarter, year, and month as separate columns so as NOT to rely on Excel's grouping.....for good reason:
Once you group a date field, you can no longer filter on the individual days.
This is likely why Timeline slicers were introduced for Excel 2013.