Pivot Table Filter for Months in Chronological Order

Copper Contributor

I have my raw data filtered chronologically by months of the year. When I create a pivot table, the months show out of chronological order. How do I get the pivot table filter drop down to display months in chronological order?

 

SaraOC_0-1639675546663.png

 

Thank you in advance!

7 Replies

@SaraOC Most likely the dates aren't real dates but texts that look like dates. Then they get sorted in alphabetical order. "10-O" thus get sorted before "1-J".

@Riny_van_Eekelen True, but when I convert it to a date the issue remains. 

SaraOC_0-1639680875082.png

 

@SaraOC But then they are still texts. What do you see when you enter a formula like =ISNUMBER(A1) where A1 would be a cell with a date?

@Riny_van_Eekelen  I get "TRUE" when I type in that formula. 

SaraOC_0-1639752935497.png

 

@SaraOC That is odd indeed!

@SaraOC 

Perhaps you may share how source data looks like mentioning how do you group dates and do you use data model with PivotTable or not.

@SaraOC 

Renumber the months as follows to solve your problem:

01-January

02-February

03-March

04-April

05-May

06-June

07-July

etc. and require a two-digit month identifier. This will keep your dates in the correct order for what you are attempting to do.