Help

IF, AND and TODAY() problems!

Topic Labels: Formulas
Solved
Jump to Solution
3343 8
cancel
Showing results for 
Search instead for 
Did you mean: 
rfrly
5 - Automation Enthusiast
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

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

image

See Solution in Thread

8 Replies 8
AlliAlosa
10 - Mercury
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')))), "", ""))

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

image

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})

image

HTH, Nathalie

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
5 - Automation Enthusiast
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.

Great! Thank you @Nathalie_Collins so much :slightly_smiling_face: