2

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

1
  • 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
    Commented Aug 24, 2018 at 14:08

2 Answers 2

3

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"); ""))
0
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/)

3
  • You call Method 1 a "1-line formula", but display it here as a 13-line formula. Even in the screenshot, it's still taking up 4 lines. So what exactly do you mean by "1-line" in Method 1? Or was it a typo?
    – TylerH
    Commented Dec 31, 2021 at 16:11
  • @TylerH lol, no typo there. "1-line formula" means that there is a single formula in a single cell that will do all the workload so there is no need for any helper columns. formula on screenshot has 4 lines because it was made that way on purpose to be more visually appealing, tho there is no need for that and it can be all put into a single line or every character of that formula can be on a separate line. "new lines" are introduced with CTRL+ENTER while typing the formula into fx bar and fx bar can be expanded with CTRL+ARROW_DOWN. this way you can create a nice overview for long formulae
    – user0
    Commented Dec 31, 2021 at 17:24
  • I see, so in that case "1-line" is not really an accurate description; "a single formula" would be more apt.
    – TylerH
    Commented Dec 31, 2021 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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