Date Format in Pivot Tables

[KimbersHulme]

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 date format) Multiple lines with the same month.  When I put the data into a Pivot table and choose my Months as a column field, the pivot table does not seem to recognise the data as a date format so the Column Headings are not in the correct order and it does not have it with the year format as above.  So the order is incorrect, i.e.    

Jan   Feb   Mar Apr  May  Jun 

Jan should be after Dec 19   Jan 20  

How can I change this as it is driving me mad.  I have checked the format of the source data and it is formatted as data with my chosen format of Feb 20 (data in cell in the formula bar shows 01/02/2020).

I am sure there is a simple setting but 6 hours after research and checking everything, changing everything I am buggered if I can find it.  Any assistance or advice will be gratefully appreciated.. 

I am on MS Office Home & Student 2016

[#2]

@KimbersHulme 

Hello, your question is not clear to me. Just be specific about what you really want with example

[#3]

@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.

[#4]

@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! :) )