5

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)

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Denise
  • 133
  • 1
  • 2
  • 10
  • "various date formats" makes me worry about the possibility to get it working... are they dates or sometimes just strings ? you can check that by clicking on it, if a calendar pops up then it's a date, otherwise it isn't and no formula will be able to get it ... – Serge insas Aug 20 '17 at 12:49
  • @Sergeinsas Oh they are strings i see. Some are self-entered that looks like "23 August" and it gets autoformatted to 23/08/2017 when viewed on formula bar. Others are generated via a form which looks like "Sat, 22 Jul 2017" but formula bar shows "22/07/2017 13:00:00". – Denise Aug 20 '17 at 12:57
  • @Sergeinsas Sorry for the double comment, but do you mean there's no way to filter the strings of date? can i just change them to dates somehow – Denise Aug 20 '17 at 12:59
  • Strings and dates are treated completely differently in formulas (and in JavaScript as well) and converting from various string formats to dates is never an easy job (at least when it needs to be completely foolproof ;) ) . The first thing to do would be to perform that conversion, search on this forum with JavaScript tag maybe ? https://stackoverflow.com/search?q=%5BJavaScript%5Dconvert+string+to+date – Serge insas Aug 20 '17 at 13:10
  • @Sergeinsas Alright thanks for the heads up! :) So when i do get them sorted into dates, am i right to say that i can use today() to compare with column M then? – Denise Aug 20 '17 at 13:14
  • Even if some are empty dates* – Denise Aug 20 '17 at 13:14
  • I'm not good at "formulas", I (almost) never use it ! sorry. But I'm sure others will answer. – Serge insas Aug 20 '17 at 13:16

1 Answers1

7

There's no today() in Query. Use now() instead:

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

Or if you want now() without time(equivalent to TODAY()), use:

todate(now())

For this to work, 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: N1:

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. There is also a more specific function:

=ARRAYFORMULA(ISDATE(M:M))

References:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • 1
    Great answer anonymous! I didnt know there must be a "date" written in the select statement or the query returns empty results. Is that to specify the data type of the string that follows? – Denise Aug 20 '17 at 19:04
  • Yes. We define the string by date. Using Query with dates is actually problematic. Because of how Excel/Sheets sees dates and how SQL sees dates.There is no today (), but there is `now()`, just in case, i haven't made it clear in the first code. – – TheMaster Aug 20 '17 at 19:44