CODEWITHSUNDEEP
google-sheetsformulasgoogle-query-language
Kevin Taylor
Kevin Taylor

Reputation: 31

How to compare a DATE with a TEXT string in a QUERY function in Google Sheets?

Goal: Return a specific value from a table of data based on a query (kinda like if VLOOKUP provided the option for multiple criteria).

Problem: The data in the source table is a value and I can't change the data source's format. When I run my QUERY function I get #N/A. I know it's due to the data type of the source table data because when I update the format to "plain text" the value works.

Here is my Query: 

=QUERY(SessionsData,"select D where B='"&TEXT(Date(YEAR(TODAY()),4,$A143),"yyyy-MM-dd")&"' limit 1",0)

I know the logic works, watch this video for a brief demo.

How can I get this comparison to return results?

Upvotes: 2

Views: 3430

Answers (1)

Mogsdad
Mogsdad

Reputation: 45760

Adapted from this answer:

The Query language has two functions to help with date comparisons.

  • The todate() scalar function will convert spreadsheet dates (like your column B) to query date values. If the value started as a datetime, it returns just the date portion.
  • The date modifier treats specifically formatted strings as query date values.

Use them like so:

=QUERY(SessionsData,"select D where todate(B)=date '"&TEXT(Date(YEAR(TODAY()),4,$A143),"yyyy-MM-dd")&"' limit 1",0)
                                    ^^^^^^^^^ ^^^^

Upvotes: 3

Related Questions