Excel 2016 Pivot Table Date Format 2

[rmarcus]

I have a file which has a date (dd/mm/yyyy)against a document reference. When I create a pivot table with the document number and date column it automatically adds "Quarter" and "Year" although the data table does not have columns headed "Quarter" and "Year". The pivot table shows the column heading of the date column but instead of showing the date it only shows the month.

The original file was created in Excel 2010 and works 100%. When I open the file in Excel 2016, it looks fine until I refresh.

Can anyone give me an explanation of what is different in 2016 and how I can avoid the "Quarter" and "Year" being added in the pivot table ?

Regards,

Ralph

 

[SkipVought]

Hi,

Of course a Pivot Table is a summarization tool.

So are you summarizing by DAYS or MONTHS or QUARTERS or YEARS? There is a way of selecting that.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rmarcus]

I am summarizing by document and want the date (ddmmyyyy)for each document.

In my data table there is a column with the date. I do not have a column with the quarter and year. The pivot is creating this.

Regards
Ralph

 

[SkipVought]

The pull those unwanted fields off your Pivot Table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Excel automatically groups dates by adding new fields: year, quarter, month. Right-click grouped field and choose "ungroup". To switch off automatic date grouping in pivot table deselect this option in excel settings.

combo

 

[rmarcus]

Thanks Combo and SkipVought for your help.

Ungrouping has done the job.

Cheers,

Ralph