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

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.

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.