0

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 [email protected] Check In
Sep 16, 2021 09:01 AM [email protected] Check In
Sep 16, 2021 04:15 PM [email protected] Check Out

desired outcome and Formula in a New tab

Column A Column B
[email protected] Formula
[email protected] 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!

Improve this question
1

1 Answer 1

Reset to default
0

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().

Improve this answer
3
  • Hi @Doubleunary, Thank you for your solution! That worked like magic! The only thing I want to change is to have a static list of email addresses in the new tab and automatically update check in/check out time if that person put their answer through the google form and leave corresponding cell empty if that person has not checked in/Checked off through google form. This way I can see who has not logged in yet. Hope this make sense. Once again thank you very much. really appreciate it!
    – Vish
    Sep 16, 2021 at 17:00
  • Your place the results from the query side-by-side with a static list of names in another tab with a vlookup() formula in that other tab. Cannot provide more detailed advice because you have not shared a publicly editable sample spreadsheet.
    – doubleunary
    Sep 16, 2021 at 17:30
  • That worked! Thank you very much again!
    – Vish
    Sep 16, 2021 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.