-1

I wish to use a generalized projection as follows in a Google query with no success =query('Form Responses 2'!A:N,"Select if(month(F)>6,year(F),year(F)-1) , N,B,C,D,E,F,G,H,I,J,K where F >= date '2011-07-01' and F <= date '2050-06-30' order by F desc, B")

Wishing to list the Year in column F in a required way.

Will be grateful if you can help me correct this.

Vishal
  • 23
  • 4

1 Answers1

1

You need to put the existing columns and your new column into an array

=arrayformula(query({A:N,if(isdate(F:F),if(month(F:F)>6,year(F:F),year(F:F)-1),F:F) },"Select Col15,Col14,Col2,Col3,Col4,Col5,Col7,Col8,Col9,Col10,Col11 where Col6 >= date '2011-07-01' and Col6 <= date '2050-06-30' order by Col6 desc, Col2",1))

I also had to use isdate() because if you have headers in your table the month() and year() functions will give an error.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37