Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# IF, AND and TODAY() problems!

Topic Labels: Formulas
Solved
1086 8
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
9 - Sun

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

8 Replies 8
10 - Mercury

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')))), "❌", "✅"))`

9 - Sun

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

5 - Automation Enthusiast

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:

Hi @rfrly,

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

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

HTH, Nathalie

5 - Automation Enthusiast

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!

5 - Automation Enthusiast

thank you :slightly_smiling_face:

@rfrly

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

5 - Automation Enthusiast

Great! Thank you @Nathalie_Collins so much :slightly_smiling_face: