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

|improve this question
  • 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) – MARK MY ANSWER 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 – MARK MY ANSWER 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 – MARK MY ANSWER 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)}

|improve this answer
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)}))

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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