0

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
1

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
0

while Nick's answer may be sufficient:

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

1]

(see the result: https://docs.google.com/spreadsheets/d/)


there are also other approaches which can lead to similarly different results:


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

3]

(see the result: https://docs.google.com/spreadsheets/d/)


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)

5]

(see the result: https://docs.google.com/spreadsheets/d/)


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

7]

(see the result: https://docs.google.com/spreadsheets/d/)


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)

9

(see the result: https://docs.google.com/spreadsheets/d/)



to fix B5 you can change formula to: =AVERAGE(FILTER(F8:F;LEFT(E8:E;4)="2017"))

(see the fix: https://docs.google.com/spreadsheets/d/)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.