Extract Month and Year from a date field for use in a chart

[queenbean84]

I am having a problem with month and year in a chart. As you see, the month/year field is not in the correct order.

https://plus.google.com/105279248984438843964/posts/i6qGXPFRHFG

This is where the data comes from:

https://plus.google.com/105279248984438843964/posts/6brz7dNWw5z

The formula in the M/Y field is =TEXT([@[Order Date]],"mm-yyyy"). This is where I believe the problem lies. How do I extract the month and the year so that the chart puts the month/year in the correct order?

 

[Amanda Wilso]

In the Pivot Chart, right click the Date filed button and select Field Settings from right-clicking menu.
In the coming Field Settings dialog box, click the Number Format button.
Now you get into the Format Cells dialog box, click to highlight Custom in the Category box, and then type the format code into the Type box, and click the OK button.
Click the OK button in Field Settings dialog box

 

[queenbean84]

When I right click on the date field, this is what I see. I don't see any of the options you mentioned. I am using Excel 2016.

https://goo.gl/photos/jf3sFJu4cu9npE9q8

 

[Amanda Wilso]

You have three ways to get to this dialog:

• Right-click a number in the Values area of the pivot table and select Value Field Settings.
• Click the drop-down to the right of the Sum of Revenue field in the areas of the PivotTable Fields list and then select Value Field Settings from the context menu.
• Select any cell in the Values area of the pivot table. From the Analyze tab, select Field Settings from the Active Field group.

 

[queenbean84]

Thanks for your help. I've tried all your suggestions but nothing works.

 

[AhoyNC]

Try changing your formula to:
=DATEVALUE(TEXT([@[Order Date]],"mm-yyyy"))
You can format the cell using Custom Format mm-yyy