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

7
  • 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 '19 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 '19 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 '19 at 16:10
  • So, the query uses the format of the data and not the underlying data?
    – John Faig
    Feb 7 '19 at 17:05
  • yes, in this case
    – user0
    Feb 7 '19 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.