3

I'm trying to select records that are >= a calculated field. Here is the query:

=QUERY('Employer Form Responses'!A:Q,"select * where A >='"&B1&"' ",1)

Column A in the data is a Date field (e.g. 9/14/2020 18:46:39) and field B1 contains

=TEXT(DATEVALUE(TODAY()-30),"YYYY-MM-DD")

The query gives me an empty result. How should I do this?

marikamitsos
  • 10,264
  • 20
  • 26
Chris Rosendin
  • 305
  • 2
  • 14

1 Answers1

5

You have to let a query know that you're comparing a date by simply adding date after the comparison operator. Below is that added to the query in your post.

=QUERY('Employer Form Responses'!A:Q,"select * where A >= date '"&B1&"' ",1)

More info on the weirdness between dates and query here: https://www.benlcollins.com/spreadsheets/query-dates/

Jeff Gibson
  • 428
  • 2
  • 9
  • 1
    Thank you! I think we're getting close. Now I get the error "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [44060]. Date literals should be of form yyyy-MM-dd." The 44060 is referring to the value in B1. – Chris Rosendin Sep 16 '20 at 16:32
  • 1
    OK - figured out the last bit. I needed to change B1 from =TODAY()-30 TO =TEXT(DATEVALUE(TODAY()-30),"YYYY-MM-DD") to get the data into the format the query expects. Thank you, Jeff Gibson! – Chris Rosendin Sep 16 '20 at 16:40