CODEWITHSUNDEEP
google-sheets
Chubaka
Chubaka

Reputation: 3155

sort a yyyy/mm column correctly in google sheet (1991/10 is always after 1991/01)

There is a google sheet here and try to calculate the monthly average price using the daily price in the Column A & B

Column C tries to get the mm/yyyy

enter image description here

Trying to sort the column E average monthly price by the column D mm/yyyy after calculating the monthly average of the price using the formula

=QUERY(B2:C,"select C, avg(B) group by C Order By C ASC")

But in the mm/yyyy column D,

1991/10, 1991/11, 1991/12 is always after 1991/01

Using the sort range result in the same sorting result. nothing changed.

enter image description here

Curious how can we correct it?

Upvotes: 0

Views: 50

Answers (1)

walter
walter

Reputation: 528

Try replacing the formula in column C with =ARRAYFORMULA(IF(A3:A <> "", YEAR(A3:A) & "/" & TEXT(MONTH(A3:A), "00"), ""))

This should pad the months with leading zeros, and allow lexical sorting to work properly.

Upvotes: 1

Related Questions