Aug 18 2022 02:56 PM
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!
Aug 18 2022 03:36 PM
SolutionAug 18 2022 04:02 PM
Aug 19 2022 12:36 AM
Have you tried sorting it? It should work.
Aug 19 2022 10:13 AM
@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!
Aug 18 2022 03:36 PM
SolutionYou could use
TEXT(A2, "yyyy/mm")
or
A2-DAY(A2)+1
and set the number format to mm/yyyy