Google-sheets – Using a query to pull data for today

google sheets

I am trying to pull the data from sheet 1, using the following formula:

=query(Sheet1!A1:V1850,"select I,A,D,E,F,H,R,S,L, M,O where I = 'TODAY()'")

So I do not have to enter todays date every time. How can it get it to pull that information?

Here is the sheet.

Best Answer

To select the rows with today's date, the query string should be of the form

=query(..., "select ... where I = date '" & text(today(), "yyyy-mm-dd") & "'")

Explanation

  1. In the query language, dates should be entered as date '2015-02-09'
  2. Therefore, one has to format today's date as yyyy-mm-dd. This is what text(today(), "yyyy-mm-dd") does.
  3. The ampersands concatenate strings, so we get date ' followed by 2015-02-09 followed by ', achieving the required format.

Remark

Putting 'TODAY()' within a double quoted string does not make it a formula: it's just the word TODAY(), treated as a part of that string. Formulas should be kept outside of strings, with their output concatenated to them as shown above.

Related Topic