What is the best way to identify exceptions to a sign in/out worksheet?

melody.cooley71056

I'm creating a sign IN/OUT sheet and am trying to build a formula that checks a checkbox if there is not a corresponding "Check Out" to a "Check In" based upon the guest's email.

I need help building the formula to match the following logic: If email AND created on date match AND there is not a Check In AND Check Out value, if true check checkbox, if false leave blank.

Any assistance is greatly appreciated.

L_123

=if(countifs(email:email,email@row, created:created,dateonly(created@row),[Check Out]:[Check Out],1)>0,1,0)

I think something like the above should do it for you (given you correct the column names)

L_123

=if(countifs(email:email,email@row, created:created,LEFT(Created@row, 8) = LEFT(@cell, 8),[Check Out]:[Check Out],1)>0,1,0)

My formula did not have the expected behavior after testing. Please try this one instead.

melody.cooley71056

I'll give it a try and post back. Thanks!