Query and compare date on google sheets

Question

I'm working on making a replica of sheet1 on to another sheet2 (same document), and query() worked fine until the column i want to filter are formula cells (LONG ones each with query, match, etc).

What i want to do is filter the rows in sheet1 where the event date in column M is upcoming (there are more filter conditions but just to simplify this is the main problem).

I don't want the rows where the date is either empty, in the past (various date formats), or where the formula give a result of empty string "".

The formulas i've tried (which gives error) - note i'm just selecting 2 columns for testing:

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND DATEVALUE(M)>TODAY() ",0)

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M>TODAY() ",0)

This formula doesn't give error but doesnt show correct data - shows all data except Jan 2017 - August 7 2017:

=FILTER(sheet1!A3:N, sheet1!I3:I="Singapore", sheet1!M3:M>TODAY())

This formula gives empty output:

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M='22 August' ",0)

I'm a newbie at google sheets & apps script so appreciate any advice from anyone Thanks!!

Answer 1

There's no today() in Query .

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M > now() ",0)

This should work theoretically, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula bar shows the correct date or time) regardless of the actual string. If not, You should manually convert all those remaining dates to correct format. The correct format of date to be entered in the query formula is date 'yyyy-mm-dd'. It doesn't matter what format the date is in sheets ( as long as Google sheets recognises this), but the actual formula must only contain date in this format for comparison. For example , to find all dates less than 31,August, 2017,

=query(A2:B6, "select A where A < date '2017-08-31'")

You can use this to figure out all the dates, which Google doesn't recognise: N:N= M:M*1 If you get an error in the helper column N, then those dates are not recognised. Even if you did not get error, it is possible that Google sheets mis-recognizes the date.

EDIT Syntax for date in Google sheets