Google-sheets – Why doesn’t “format A ‘yyyy-mm’ options no_values” strip the day from the output

google sheetsgoogle-sheets-datesgoogle-sheets-query

I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to yyyy-mm and then specify options no_values to ensure that the output does not include the day, at all (documentation reference)?

Example data is below. Notably, the output is sorted by day, which I do not want. I only want the output to be sorted by month. Note that this example contains only one month for simplicity.

input:

Date Val1
8/5/2015 10
8/27/2015 13
8/14/2015 4

formula:
=query(A2:B,"select A,B where A is not null order by A asc format A 'yyyy-mm' options no_values",0)

output (when looking at cells):

Col1 Col2
2015-08 10
2015-08 4
2015-08 13

output (when looking at formula bar):

Col1 Col2
2015-08-05 10
2015-08-14 4
2015-08-27 13

Best Answer

Try this:

=arrayformula( 
  query( 
    { text(A2:A, "yyyy-mm"), B2:B },
    "select Col1, Col2 
     where Col1 is not null 
     order by Col1 asc", 
    0 
  ) 
)
Related Topic