My current array formula is `=ARRAYFORMULA(IF(A8:A <> ""; YEAR(A8:A) & "/" & MONTH(A8:A); ""))`

which turns "16 january 2016" etc. into "2016/1"

However when I later pull them elsewhere they're in the wrong order: 2017/1 2017/11 2017/2

This is because months are showing as '1' instead of '01'. How can this be remedied?

Example sheet

• We prefer questions to be self-contained. While adding a link to a sample sheet is good for supplemental information, on its own it is problematic. What happens when you stop sharing that sheet? This question becomes absolutely worthless. Can you edit your question to include enough information so someone has an opportunity to answer without following the link? – ale Aug 24 '18 at 14:08

Use TEXT function to format the month column. Your array formula would change to:

``````=ARRAYFORMULA(IF(A8:A <> ""; YEAR(A8:A) & "/" & TEXT(MONTH(A8:A);"00"); ""))
``````
• Thanks so much. I thought I had tried this to no success. Must have been a syntax error. – Ali reid Aug 28 '18 at 1:59

while Nick's answer may be sufficient:

`=ARRAYFORMULA(IF(A8:A<>"";YEAR(A8:A)&"/"&TEXT(MONTH(A8:A);"00");""))` # METHOD 1:

``````=SORT(QUERY(A8:C;

"select C, sum(B) where C != '' group by C order by C asc label sum(B)''");

DATE(YEAR (INDEX(QUERY(A8:C;

"select C, sum(B) where C != '' group by C order by C asc label sum(B)''");;1));

MONTH(INDEX(QUERY(A8:C;

"select C, sum(B) where C != '' group by C order by C asc label sum(B)''");;1));

DAY(1));1)
``````

this is a 1-line formula in which format of date stays as `2016/1` and soring is done first by year and then by month # METHOD 2:

the result is the same as in the method 1, however with the usage of side calculations in hidden columns using a formula:

``````=QUERY(A8:C;"select C, sum(B) where C != '' group by C order by C asc label sum(B)''"; 0)
``````

and then getting the desired data with a formula:

``````=SORT(H8:I;DATE(YEAR(H8:H);MONTH(H8:H);DAY(1));1)
`````` # METHOD 3:

``````=SORT(QUERY(A8:C;

"select C, sum(B) where C != '' group by C order by C asc label sum(B)''");

MONTH(INDEX(QUERY(A8:C;

"select C, sum(B) where C != '' group by C order by C asc label sum(B)''");;1));1)
``````

this is a 1-line formula in which format of date also stays as `2016/1` and soring is done first by month and then by year (eg. it will create clusters based on months) note the change in B5 # METHOD 4:

the result is the same as in the method 3, however with the usage of side calculations in hidden columns using a formula:

``````=QUERY(A8:C;"select C, sum(B) where C != '' group by C order by C asc label sum(B)''"; 0)
``````

and then getting the desired data with a formula (note the change in B5):

``````=SORT(H8:I;MONTH(H8:H);1)
`````` to fix B5 you can change formula to: `=AVERAGE(FILTER(F8:F;LEFT(E8:E;4)="2017"))`