google-sheetsgoogle-sheets-formuladatefilter

Google Sheets filter by todays date


I have created a google sheet of sporting events, this then filters to different websites using the ninjatables wordpress plugin.

Not all websites need to display all the games.

The 'Games' sheet contains a list of all the events throughout the year, I then have a query to filter the results into individual sheets for individual sites, displaying only certain sporting events for that site, such as;

Site 1 = Rugby and Football

Site 2 = Football and Basketball

Everything so far works fine.

I am now trying to add a date filter so that games before todays date are no longer visible.

=query(Games!A:Y, "where A > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and (F = 'Football') or (F = 'Rugby')",1)

If I change the greater than (>) sign to less than(<), it filters out the results to only show the games before todays date just fine, but for some reason my current query does not filter out games before today.

I have tried changing the formating on my date column on the games sheet (mm/dd/yy, dd/mm/yyyy, yyy-mm-dd, etc) not sure that that has anything to do with it, but couldnt hurt to try.

If anyone could shed any light as to where I may be going wrong it would be greatly appreciated.


Solution

  • Solution:

    You can use greater than or equal >= as an operator.

    EDIT: Boolean operations are evaluated left to right, so A > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and (F = 'Football') are evaluated first, then this is or'd with (F = 'Rugby').

    This is fixed by grouping the column F conditions with parenthesis.

    =query(Games!A:Y, "where A >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and ((F = 'Football') or (F = 'Rugby'))",1)
    

    This will display entries greater than or equal to today's date, and if column F is (Football or Rugby)

    Sample Query:

    enter image description here