Forum Discussion

KimbersHulme's avatar
KimbersHulme
Copper Contributor
Feb 27, 2020

Date Format in Pivot Tables

Hi, I have read a lot of posts on dates in Pivot tables.  I have a column of dates data formatted as Header Months     data entered  Oct 19  Nov 19  Dec  19  Jan 20   Feb 20   Mar 20  Apr 20 etc (US ...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 28, 2020

KimbersHulme Make sure that you use the column with your entered dates (like 01/02/2020) as the column field. Then group the dates by Month AND Year. When you group only by month, Excel will create your table with month headers from Jan to Dec. Values from both December 2019 and December 2020 end-up in the same column for Dec. I suspect that adding the Year to the grouping resolves your problem. The picture below (Excel for Mac, but should look similar on a PC) demonstrates this.

.... and the attached workbook contains a working example.

KimbersHulme's avatar
KimbersHulme
Copper Contributor
Feb 28, 2020

Riny_van_Eekelen 

 

thank you this has solved the problem however, the old Pivot table format (without Grouping) used to work perfectly and the visual look of the table was simpler to look at.  I think I need more practice on my newer version of excel.  Old table looked like the attached. 

 

thank you.  it would have taken me days to figure this out. 

 

(wish I hadn't upgraded now! 🙂 )

 

Resources