Why doesn't “format A 'yyyy-mm' options no_values” strip the day from the output?

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

Answers 2

  • It appears that Google Sheets does not support the options clause. This is acceptable, based on the documentation reference:

    Note that data sources are not required to implement the query language, or if they do, to implement all features of the language. Unless you have reason to believe otherwise, you should not depend on a data source to implement all features of this language.


  • 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 Questions