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?
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.