0

I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September.

I am using the following formula. The other columns work fine because they contain just a single word. Since column 1 is not a single word it doesn't work. How can I use a condition that contains September?

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1YcJh57OVzaeUfHhR-xHmvYums1KIwrbGYoRouXAE7k8/edit#gid=661495879", "Leads!A2:L30000"),"SELECT Col2 WHERE ((Col9 = 'Peugeot') OR (Col10 = 'Peugeot')) AND (Col14 = 'Septiembre')")

0

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

contains - A substring match. whole contains part is true if part is anywhere within whole. Example: where name contains 'John' matches 'John', 'John Adams', 'Long John Silver' but not 'john adams'.

Other alternatives are to use matches with a regular expression and like which supports two wildcards, % (zero on more characters) and _ (anyone character). Please read the above reference for details.

0

Working with dates within a query can become quite tricky.

Use the FILTER function outside the query to filter your query according to date as an alternative solution.

Suppose your dates column in Leads!A2:L30000 is column O. Then, your formula will become:

=filter(QUERY(IMPORTRANGE("1YcJh57OVzaeUfHhR-xHmvYums1KIwrbGYoRouXAE7k8", "Leads!A2:L30000"),"SELECT Col2 WHERE ((Col9 = 'Peugeot') OR (Col10 = 'Peugeot'))"), Leads!O2:O30000 >= DATE(2019,9,1), Leads!O2:O30000 <= DATE(2019,9,30))

This way we filter our query results to just the month of September 2019.

-2

Try

Col14 matches 'September'

O

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.