Google-sheets – Using TODAY function with QUERY

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-query

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

Best Answer

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

Related Topic