Sort by chronological date for 'mm/yyyy' format?

[PatDools]

Hello,

I have a calculated field that extracts month and year from a date field using the TEXT function:

TEXT(A2, "mm/yyyy")   Example:  01/2022

The problem I've having is that my dataset spans a number of years, and so it sorts in my Pivot Table by month and not by month/year as shown below:

01/2021

01/2022

02/2021

02/2022

03/2021

03/2022

04/2021

04/2022

05/2021

I need it to sort by chronological date like this:

01/2021

02/2021

03/2021

04/2021

How do I get it to sort by month and year in chronological order?

Thank you!

[#2 · Accepted solution]

@PatDools 

You could use

TEXT(A2, "yyyy/mm")

or

A2-DAY(A2)+1

and set the number format to mm/yyyy

[#3]

Thank you, Hans - this delivers the right field format, but doesn't help with the sorting problem I described above. How can I get these dates to sort in month/year order?

[#4]

@PatDools 

Have you tried sorting it? It should work.

[#5]

@Hans Vogelaar OK - got it, I had missed that my Running Total was associated with my original formula field instead of the updated one I inserted from you: (=$K2-DAY($K2)+1).  This worked - thank you!