0
votes

I would like to use the today function in a query. Right now I have to manually change the date each morning, which is time consuming. The query is:

=QUERY(StageTracking!A:W, "SELECT C where A =date'2021-05-13'")

When I try

=QUERY(StageTracking!A:W, "SELECT C where A =today()")

I get a #VALUE error.

I know it's just a syntax thing I'm not catching but I have tried many variations on the line above.

3

3 Answers

2
votes

Let me offer another (perhaps simpler) option, given what I can tell from your post info.

Add a header in the top cell of your results column and put the following formula into the second cell of that otherwise empty column:

=FILTER(StageTracking!C2:C,StageTracking!A2:A=TODAY())

ADDENDUM (after seeing the actual sheet):

This is an excellent case in point of why it is always most efficient and effective to share a link to a sheet, since your formula attempts as originally posted (and mine as posted above) would not work with your actual layout and goal.

I've added a new sheet ("Erik Help").

First, I un-merged Rows 2-8 and simply increased the height of Row 2. There was no reason to merge those rows; and merging nearly always causes issues, especially in ranges where formulas or reference ranges are involved.

Next, I deleted your original A2 formula (=QUERY(StageTracking!A1:W1000,"select C where A = '06/23/2021'",1)) and replaced it with the simple =StageTracking!C1, which accomplishes the same thing. Again, I'm not sure what led to the long formula, but it was unnecessary.

I then deleted all of your individual erroneous formulas from B2:K2 and replaced them with one formula in B2:

=FILTER(FILTER(StageTracking!E2:W,StageTracking!A2:A=TODAY()),ISODD(COLUMN(StageTracking!E1:W1)))

This formula first creates a FILTERed array of everything from E2:W where A2:A = TODAY(). Then a second FILTER is applied to bring in only the odd columns.

NOTE: currently, while the formula is working, you will see no results in E2:W because you don't have any data for TODAY in your StageTracking sheet yet. Once you add data for today's date, you will see the formula populate B2:K2. (Or, you can simply add -1 after TODAY() in the current formula if you want to see the results from "yesterday" temporarily, in order to be sure the formula is, in fact, working.)

1
votes

Try this:

=QUERY(StageTracking!A1:W1000,"select C where A = '06/23/2021'",1)

or

=QUERY(StageTracking!A1:W1000,"select C where A = date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)
0
votes

Take a few minutes to review the scalar functions supported in the QUERY() function.

https://developers.google.com/chart/interactive/docs/querylanguage#scalar_functions

You can use YEAR(), MONTH(), DAY() or NOW(). NOW() is a compete datestamp including time, so that would require more effort.