Pass a Date value from a cell in Google Sheet formula-CodePudding

How do I re write this query for Google sheet, so that the dates in column B can be taken from a cell say X1 and X2

=QUERY(master!A:N,"select B,D,H where B>=date '2021-10-01' and B<=date '2021-10-31' and (E='3-MonoYarnSupply' or E='4-MultiYarnSupply') label B 'Date',D 'Particulars',H 'Quantity Supplied'")

Expected formula

=QUERY(master!A:N,"select B,D,H where B>X1 and B<=X2 and (E='3-MonoYarnSupply' or E='4-MultiYarnSupply') label B 'Date',D 'Particulars',H 'Quantity Supplied'")

Where X1 = 01-10-2021 and X2 = 31-10-2021

CodePudding user response：

You can split the formula and use string concatenation logic to refer to other cells like this:

=QUERY(A:N,"select B,D,H where B>=date '" & TEXT(X1, "YYY-MM-DD") & "' and B<=date '" & TEXT(X2, "YYY-MM-DD") & "' and (E='3-MonoYarnSupply' or E='4-MultiYarnSupply') label B 'Date',D 'Particulars',H 'Quantity Supplied'")

CodePudding user response：

B >= date '"&TEXT(X1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(X3,"yyyy-mm-dd")&"'

Thank you @Gangula. Qualifying the date values as above in the query helped to achieve what I required. Thank you for your time.