How to query Google spreadsheet cell for date before 2016-02-16

1
votes

I have a Google spreadsheet containing subscriptions. I need to query that spreadsheet to find out what subscriptions have expired. By searching on the internet, I've found I can query the spreadsheet to find out which subscribers expire after a certain date (2016-02-16 in this example) with this command:

=QUERY(Everyone!$A:$ZZ, "SELECT * WHERE H >= date '2016-02-16' ORDER BY H",-1)

(The expiration date is in column H.)

If the date in question is in a certain cell (E2 in this example) I can do that here too:

=QUERY(Everyone!$A:$ZZ, "SELECT * WHERE H >= date '" & text(E2,"yyyy-MM-dd") & "' ORDER BY H",-1)

I want to know the subscribers that have an expiration date before 2016-02-16. I thought I could just replace the >= with <, but that returns no results. (I know I have dates before 2016.)

So my question:

How to query in a Google spreadsheet to find all dates less than a specified date?

2
google-sheetsgoogle-query-language

2 Answers

3
votes

If you look at the bottom of your sheet you will see your data. The issue is with Order By. The null cells in H are sorted first. Add AND H IS NOT NULL and it should solve this.

=QUERY(Everyone!A:J, "SELECT * WHERE H < date '2016-02-16' AND H IS NOT NULL ORDER BY H",-1)

Another option would be to have the exact row numbers Everyone!A1:J16. Then blank cells are not an issue.

0
votes

Why not just make use of a filter? You can do that such as

=filter(Everyone!$A:$ZZ, H4:H>H3)

//Where H3 is where you put the date of your choice.