1

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet feeds the "Week 1 data" worksheet, where I manipulate the data and it contains the query. The data range query returns no results. All other queries work.

my spreadsheet

  • its because dates in E column are not recognized as dates by query (this can be checked if you set Automatic formatting for E column) – user0 Feb 7 at 4:56
  • Bummer. I thought about that and tested all of the dates involved using the =isDate() formula and it returned all true values. I also tried replacing the data in column E with =DateValue() functions and the query still didn't work. – John Faig Feb 7 at 15:06
  • half true... yes, E colum "does" have dates, but they are not full dates. meaning 'Week 1'!D$3 is in the format 3/4. so you import it as 3/4 and internally convert it to 3/4/2019 by using cell formatting, but QUERY still sees it as 3/4 – user0 Feb 7 at 16:10
  • So, the query uses the format of the data and not the underlying data? – John Faig Feb 7 at 17:05
  • yes, in this case – user0 Feb 7 at 17:12
0

={B1:F1; QUERY(ARRAYFORMULA({B2:D, TO_DATE(VALUE(E2:E)), F2:F}), 
         "select * where Col4 >= date '"&TEXT($G$1,   "yyyy-mm-dd")&"' 
                     and Col4 <= date '"&TEXT($G$1+5, "yyyy-mm-dd")&"'", 0)}

0

=ARRAYFORMULA(IFERROR(VALUE(
 {B1:F1; QUERY(ARRAYFORMULA(TO_TEXT({B2:D, VALUE(E2:E), F2:F})), 
         "select * where Col4 >= '"&VALUE(G1)&"' 
                     and Col4 <= '"&VALUE(G1)+5&"'", 0)}),
 {B1:F1; QUERY(ARRAYFORMULA(TO_TEXT({B2:D, VALUE(E2:E), F2:F})), 
         "select * where Col4 >= '"&VALUE(G1)&"' 
                     and Col4 <= '"&VALUE(G1)+5&"'", 0)}))

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.