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' ")