0

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=7 
 and Col1 = 'TODAY()' 
 and Col1 = 'TODAY()+1' 
 group by Col7 
 label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Previously I was using THIS formula (see below). But with this one, I would have to change the date manually every day. Not ideal but I was managing.

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=8 
 and Col1 >= date '2019-08-20' 
 and Col1 <= date '2019-08-21' 
 group by Col7 
label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Is it possible to use the TODAY() function here or should I be looking at other avenues to solve this?

I have included a sample copy of my workbook for anyone to play with. You will find the first formula in J5 and the second formula in J27.

SAMPLE WORKBOOK

3

Instead of

date 'TODAY()'

use

date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'"

NOTES:

  • The last " should be placed in a proper place in order to have a text value as the second argument of QUERY.
  • 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis.

Try this

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=7 
 and Col1 = '"&TEXT(TODAY(), "yyyy-mm-dd")&"' 
 and Col1 = '"&TEXT(TODAY() + 1, "yyyy-mm-dd")&"' 
 group by Col7 
 label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Related

0
  • paste this in J5 cell:

    =ARRAYFORMULA(IFERROR(QUERY(QUERY({TO_DATE(INT(A$3:A)), B$3:H, HOUR(A$3:A)},
     "select Col7,sum(Col3),sum(Col5),sum(Col8) 
      where Col1 is not null 
        and Col9 = "&TEXT(INDIRECT("J"&ROW()-1), "h")*1&" 
        and Col1 >= date '"&TEXT(TODAY()-1, "yyyy-mm-dd")&"' 
        and Col1 <= date '"&TEXT(TODAY(),    "yyyy-mm-dd")&"' 
      group by Col7", 0), "offset 1", 0)))

    0


  • then select and copy J5 and paste in J27, J49, etc. wherever you need it

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.