How to compare two dates and time in Query

1
votes

I have a sheet created for live attendance in a class with two tabs (Sheet1 & Sheet2), In sheet1, I have two columns date and time. In sheet2, I have a column of numbers (Column C) and a row of date and two rows of times (open and close time).

What I need is to write a formula that query sheet1 and select column D if :

  1. the date is first column, is equal to the date in sheet2(row1)
  2. the time in sheet1(column 2) is greater than opentime in sheet2(row2)
  3. the time is sheet1(column2) is lower than closetime in sheet2(row3) and finally;
  4. the number in Sheet1 (columnD) is equal to the number in sheet2(column C)

This is what I tried:

=iferror(if(QUERY(sheet1!$A:$D, 
"select D 
where A = datevalue '"&TEXT(D$1, "MM-dd-yyyy")&"' 
and B >= timevalue '"&TEXT(D$2, "HH:mm:ss")&"' 
and B <= timevalue '"&TEXT(D$2, "HH:mm:ss")&"' 
and C = "&$C5, 0)=$C5,"✅", "❌"),"❌")

I'm not sure what is wrong. It's not working. Here is a shared google sheet: https://docs.google.com/spreadsheets/d/1zPZQgFlbz19ULu19I8-z-8m9E1vdfYMKRaFW9IycCtg/edit?usp=sharing

1
google-sheetsfiltergoogle-sheets-formulaarray-formulasgs-vlookup
not rly sure how you wish to populate it... – player0

1 Answers

1
votes

if sheet1 is like:

0

then you can do (and drag it to the right):

=ARRAYFORMULA(IFERROR(VLOOKUP($C$5:$C, 
 FILTER({sheet1!$D$3:$D, sheet1!$C$3:$C}, 
 sheet1!$A$3:$A=D1, sheet1!$B$3:$B>D2, sheet1!$B$3:$B<D3), 2, 0)))

0