Google Sheets: query to convert column to date and compare to another date

Question

I need to compare a text column, which contains a string that represents a date in the format dd/mm/yyyy, to another date.

The range is in another sheet. I cannot edit it to format the column to a date field:

I need something like this:

=query(importrange(...); "select A, B WHERE C >= '2017-01-08'")

Of course this doesn't work because only Aldo row will be visible. Is there any way to convert, in the query function, the column C to a date or to a string with the pattern yyyy-mm-dd to compare it to the right hand side value of the comparison?

Answer 1

Cumbersome but seems to work:

Import (say to Sheet1!A1):

=importrange(" k e y ","Sheet1!A:C")

Add a column with recognisable date formats (say in D1):

=ArrayFormula(datevalue(C1:C))

then apply the query:

=query(Sheet1!A:D,"select A,B,C where D >= date '2017-01-08' ")

Answer 2

I know this is WAY late, but I ran into this same problem a couple of years ago and fixed it by doing the following:

Cell A1 contains: 2/21/2018

Cell A2 contains: =DATEVALUE(B1)

which results in 43152 and I copy into the QUERY statement:

Cell A5 formula: =QUERY(Sheet!Range, "select B, C, E where B=43152")

But, to make it somewhat better by only having to type in the date, I used the CONCATENATE function to create the text "select B, C, E where B=43152" in a cell and then referenced that cell in my QUERY, like so:

Cell B1: =CONCATENATE("select B, C, E where B=", A2)

Cell A5 formula: =QUERY(Sheet!Range, B1)

This works well for me and I hope it helps someone else or at least gives someone an idea.