'Query and compare date on google sheets

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!!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source