Might be easier to add a column C with a formula such as:
=if(and(B1>eomonth(Sheet2!$A$2,-1),B1<=eomonth(Sheet2!$A$2,0)),"s",)
and apply a simplified query such as:
=query('Calculated Pay [view]'!B1:C1000,"Select B where C ='s' ")
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))
Best Answer
The following formula does this:
It constructs
date(year(today()), 4, 6), which is the 6th of April of the current calendar year. If this date is prior to today, it is the output. Otherwise, the year is reduced by 1.