How to make a query that returns rows if deadline is two weeks / one month / two months from today, etc

Question

I have a spreadsheet that contains several columns with project information. I want to modify:

=QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 )

so it returns values in L that fall within a specific date range. The data ranges are located in specific cells — in this case P1 and P2.

I need to repeat the operation for different date ranges.

PS. Note that in P1 the cell contains the formula =today() and P2 = P1+14.

Answer 1

Assuming the P1 and P2 cells mentioned are in the same sheet as the query, please try:

=QUERY(Compilation!A2:L," select A, B, F, I, L where L>date '"&TEXT(P1,"yyyy-mm-dd")&"' and L < date '"&TEXT(P2,"yyyy-mm-dd")&"' order by A desc", 0)

If you wish the P1 and P2 dates included in any selection, add = between the angle brackets and date.

I have changed the sort order to avoid blank rows at the top.