Code Guy
Code Guy

Reputation: 3198

Unable to fetch query formula results between 2 dates using sheets

I have a sheet where I need to filter the rows based on 2 dates B1 and B2. I have tried with the formula

Make a copy of the worksheet

To view the worksheet

enter image description here

=QUERY(arrayformula({IFERROR(DATEVALUE(REGEXEXTRACT(QUERY(Work_Hours2019!A1:C,"SELECT A"), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(QUERY(Work_Hours2019!A1:C,"SELECT A"), "\d{2}:\d{2}[A|P]M$"))),QUERY(Work_Hours2019!A1:C,"SELECT B, C")}), "SELECT Col1, Col2, Col3 where Col1 > date '"&TEXT(DATEVALUE(B1),"yyyy-mm-dd")&"' ")

Upvotes: 0

Views: 33

Answers (1)

JPV
JPV

Reputation: 27312

See if this works

=FILTER (Work_Hours2019!A1:C, datevalue(REGEXREPLACE(Work_Hours2019!A1:A, " at (.*)$",))>=B1, datevalue(REGEXREPLACE(Work_Hours2019!A1:A, " at (.*)$",))<=B2)

enter image description here

Upvotes: 1

Related Questions