Skip to main content
Solved

IF, AND and TODAY() problems!

  • May 23, 2019
  • 8 replies
  • 60 views
R
  • rfrly
  • Participating Frequently

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

Best answer by Nathalie_Collin

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

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • May 24, 2019

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

R
Nathalie_Collin
Forum|alt.badge.img+18

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

R
Justin_Barrett
R
  • rfrly
  • Author
  • Participating Frequently
  • May 24, 2019

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


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
Forum|alt.badge.img+18

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

R
  • rfrly
  • Author
  • Participating Frequently
  • May 24, 2019

Hi @rfrly,

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

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

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!

R
  • rfrly
  • Author
  • Participating Frequently
  • May 24, 2019

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


thank you :slightly_smiling_face:

Nathalie_Collin
Forum|alt.badge.img+18

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

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

R
A
R
  • rfrly
  • Author
  • Participating Frequently
  • May 24, 2019

@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:

Nathalie_Collin
Terms & ConditionsAccessibility statement