Google Sheets - Multiple if statements with and function

Question

I am trying to build a check in/ check out tracker for my team using google forms and google sheets. The idea is that once Someone submits their response through google form, it comes to google sheet. There I would match the data against my existing staff list and determine who checked in and who has not. I have google forms part down but I am struggling with matching data with the existing staff list.

This is how I set that up and that is how my dataset and formula look like.

Dataset in Original tab (Check In / Check Out tab)

Column A	Column B	Column C
Sep 16, 2021 08:05 AM	ABC@XYZ.com	Check In
Sep 16, 2021 09:01 AM	DFG@XYZ.com	Check In
Sep 16, 2021 04:15 PM	ABC@XYZ.com	Check Out

desired outcome and Formula in a New tab

Column A	Column B
ABC@XYZ.com	Formula
DFG@XYZ.com	Formula

*A1 = email address

=If(AND('Check In / Check Out'!B:B = A1, 'Check In / Check Out'!D:D = Today(),'Check In / Check Out'!C:C = "Check in"), "Checked in", if(AND('Check In / Check Out'!B:B = A1, 'Check In / Check Out'!D:D = Today(),'Check In / Check Out'!C:C = "Check Out"),"Checked out", "Not Checked in"))

I tried slightly different variations of the above formula but no success. Can anyone please suggest how can I get the desired outcome? Thank you very much in advance!

Answer 1

Assuming that your data table has headers in row 1 and the data starts on row 2, use query() with a pivot clause to get the last check in and check out on the current date like this:

=query( 
  'Check In / Check Out'!A1:C, 
  "select B, max(A) 
   where toDate(A) = date " & text(today(), "'yyyy-mm-dd'") & " 
   group by B 
   pivot C", 
  1 
)

The results roughly match your expected result table structure. Note that the formula will not list staff that have failed to check in and also failed to check out on the current date.

To place these results side-by-side with a static list of names in another tab, use vlookup().