SOLVED

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

New Contributor

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!

4 Replies
best response confirmed by PatDools (New Contributor)
Solution

@PatDools 

You could use

 

TEXT(A2, "yyyy/mm")

 

or

 

A2-DAY(A2)+1

 

and set the number format to mm/yyyy

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?

@PatDools 

Have you tried sorting it? It should work.

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