# QUERY most recent date prior to today

I have list of dates in column R that are spaced by two week intervals, so 6/1/16, 6/15/16, 6/29/16, etc.

In columns S to AF I will mark "y" or "n" each day on the row corresponding to the date, during the two weeks until the next date.

I would like to calculate the percentage of "y" entries in S:AF for the Current Period in cell P12.

I have `=(countif(S16:AF16,"y")/counta(S16:AF16))` which is accurately calculating the percentage of "y" entries in row 16, but I am trying to figure out a query that will select the row with the Current Period (i.e. The most recent date prior to today's date). So if today is 6/1/16, I want it to give me the percentage of "y" entries in the row with 6/1/16. If today is 6/14/16, I want it to give me the percentage for the row with 6/1/16. But when today is 6/15/16, I want it to start giving me the percentage for the row with 6/15/16, and so on.

I've tried a few different query setups and ended up with something so jumbled that I don't even know where to begin now. I was trying to do something where it would query all the dates prior to `today()` and then select the MAX of those, but I couldn't get it to work.

I finally figured out this query, which finds all the dates prior to today:

``````=query(R15:AF,"select R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF where R < date '" & text(today(),"yyyy-MM-dd") & "'",0)
``````

I can put a `MAX()` around this to get only the most recent date. However, I still can't figure out how to apply the `=(countif(S16:AF16,"y")/counta(S16:AF16))` equation so that this percentage is returned for whatever the most recent date is.

• In the description, you said that on 6/15, the row for 6/15 should be selected, and I went with that (the condition being <= today()). This contradicts your own formula where you write R < ... today().
– user79865
Jun 12 '16 at 23:46

I would use `filter` instead of `query`. The function

``````=counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))
``````

counts the number of nonempty entries in the row with the date that falls after today()-14 but not later than today(). The logic is simple: filter the rows, returning the content of their columns S through AF, then apply `counta`.

To get the count of entries with "y", a second filter is used:

``````=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y"))
``````

The final result, the proportion, is obtained by dividing the latter by the former:

``````=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y")) / counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))
``````