0

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.

dataguyreg
  • 11
  • 2

3 Answers3

2

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.)

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • the result came up blank, but I really appreciate the feedback. Here is a copy of the 2 pages I'm working with. . . https://docs.google.com/spreadsheets/d/1DJMuIxjgfI0V9elGHpZxj4x2gQ1miCGhFquNrOZq_FU/edit?usp=sharing – dataguyreg Jun 24 '21 at 11:44
  • See my post ADDENDUM above. (As I say there, this is a prime example of why it's important to share a sheet.) – Erik Tyler Jun 24 '21 at 16:17
  • Wow! Thank you! Turns out it was a lot more complicated than I thought it was going to be. Thank goodness for arrays! Best regards. – dataguyreg Jun 25 '21 at 12:14
  • You're welcome. If you would, please take a moment to click the checkmark that designates the above post as "Best Answer." This lets the contributor community know at a glance from the main dashboard which issues have been fully resolved; and it allows future site visitors to quickly find relevant answers. – Erik Tyler Jun 25 '21 at 14:33
1

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)
Rajput
  • 605
  • 3
  • 12
  • I get a #N/A error. My hope is to get that value (on a dashboard) to update automatically. Thanks for replying so quick! – dataguyreg Jun 23 '21 at 15:13
  • You need to share the sample sheet as well you need update the question with further requirements. – Rajput Jun 23 '21 at 15:16
  • https://docs.google.com/spreadsheets/d/1DJMuIxjgfI0V9elGHpZxj4x2gQ1miCGhFquNrOZq_FU/edit?usp=sharing – dataguyreg Jun 24 '21 at 11:43
0

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.

JohnA
  • 1,058
  • 6
  • 12