IF, AND and TODAY() problems!

[rfrly]

Hey folks,

I’m trying to make a check in/ out system for a music venue/ studio so they can see which members of staff are on site at any given time. I’ve used two forms where staff can check in and out which populates a table. That side of things is working fine.

The following formula shows whether someone is currently checked in:

IF(AND({time in},{time out}),"❌",IF(AND({time in},NOT({time out})),"✅",BLANK()))

I’d like the formula above to also show an :x: for those who haven’t signed out on a previous day (So the :white_check_mark: s are only for people signed in on the current day). I’ve tried to do this by comparing the most recent check in to the days date using the TODAY() function but it appears to be including the previous day as well (which I really don’t understand).

Hopefully that makes sense. I’ve been staring at Airtable for far too long today and might be going mad! Any help will be greatly appreciated.

Many thanks,
Ross

[AlliAlosa]

Hi there! I think the following should do the trick!

IF({Time In}, IF(OR(AND({Time In}, {Time Out}), AND({Time In}, IS_SAME(DATETIME_FORMAT({Time In}, 'M/D/YYYY'), DATEADD(TODAY(), -1, 'day')))), "❌", "✅"))

[Nathalie_Collin · Accepted solution]

Hi and Welcome @rfrly,

This should do it

IF(IS_BEFORE({Time In}, TODAY()),"❌",IF(AND({Time In},{Time Out}),"❌",IF(AND({Time In},NOT({Time Out})),"✅",BLANK())))

[rfrly]

Nathalie_Collins:

IF(IS_BEFORE({Time In}, TODAY()),“ :x: ”,IF(AND({Time In},{Time Out}),“ :x: ”,IF(AND({Time In},NOT({Time Out})),“ :white_check_mark: ”,BLANK())))

Brilliant, thank you!

I have another table called “staff” that is linked to the “check in” table. By referring to that formula would it be possible fetch the current check in status of each staff member and show that on the “staff” table? For example:

Mark S | :white_check_mark:
Joe B | :white_check_mark:
Megan R | :x:

Many thanks,
Ross :grinning_face_with_smiling_eyes:

[Nathalie_Collin]

Hi @rfrly,

Thank you. I would link STAFF into the CHECK IN sheet and then concatenate them

CONCATENATE(Staff," | ",{Here Today})

HTH, Nathalie

[rfrly]

Thanks @Nathalie_Collins. Does that mean it wouldn’t be possible to have the current check in status show on the staff table? I’ve had a play with look up and rollup but so far no luck. Ideally I’d just have a Staff table view with all the staff members names and then their current sign in status next to it!

[rfrly]

thank you :slightly_smiling_face:

[Nathalie_Collin]

@rfrly

Yes, you can go that way as well. I created this base as it demonstrates it much better than I could in text.

Airtable

Checkin/Checkout - Airtable

Explore the "Checkin/Checkout" base on Airtable.

[rfrly]

Great! Thank you @Nathalie_Collins so much :slightly_smiling_face: