Requesting help with conditional IF formula for makeshift status field

AnnicaT · ‎Jun 24, 2019

Hello!
I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance.

I have the following columns that should be considered in the formula:
{End Date} = Formula field
{BiU} = Checkbox field
{Last Shared} = Date field
{Next Share} = Formula field

What I want to achieve is:
If {BiU} is NOT checked = ‘-’
If {BiU} is checked AND TODAY() is after {End Date} = :white_check_mark:
If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = :ballot_box_with_check:
If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = :link:

Any and all help would be greatly appreciated.

AnnicaT · ‎Jul 01, 2019

Oh, and I have a column that cleans up the dateformat of {Next Share} if that matters.
Can you do that already IN {Next Share}?

IF({Next Share (formula)}, DATETIME_FORMAT({Next Share (formula)}, 'YYYY-MM-DD'))

AlliAlosa · ‎Jul 01, 2019

Hi there,

Thank you so much!! This helped a lot. I think the following should work, it’s a bit complicated and hard to configure without seeing the base itself so let me know if any part doesn’t perform as it should.

IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", IF(IS_BEFORE(TODAY(), {Start}), "🔗", IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))),"🎟")), "-")

To answer your latest question about {Next Share}, yes, you can certainly do that within the formula. The following should do the trick (I also worked in the additional clause you said you were going to add):

IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 2, 'days')))), 'YYYY-MM-DD'))

AnnicaT · ‎Jul 01, 2019

For some reason all results are now “-”

This one works almost perfectly except that I have two occurances of ERROR!

Both share the same base They are one day events that has {Start} and {End Date} 2019/07/07.
{Last Shared} 2019/07/01
{ :arrows_counterclockwise: Frequens}=‘Low’
{Tickets}=‘No’

Unfortunately I can’t share the base here which would have helped a lot.

AnnicaT · ‎Jul 01, 2019

OMG! [Massive face palm] I doublechecked and have of course used English in one place and no wonder it didn’t work. Corrected it and now works. Will double check in a sec to see that everything works there.

The ERROR! though is not a translation error.

AnnicaT · ‎Jul 01, 2019

There are some anomalies. Working on scrubbing base of telling data and translating so I can share with you and you can have a look around.

AnnicaT · ‎Jul 01, 2019

How does one share a base (in the forum)?

Just by link or?

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

AlliAlosa · ‎Jul 01, 2019

You shared it! Thank you, this definitely helps to make better sense of what you’re looking for :slightly_smiling_face: The share link you posted does allow anyone to enter and change stuff though, so I would make sure that is a copy of your real base. For future reference, I typically share a “read-only” link and leave the “Allow users to copy data in this base” box checked. This allows someone to make a copy of the base rather than editing the one you have saved. But this is all a matter of preference!

I took a closer look and found an error I made in the formula I gave you for {Next Share} :frowning: If { :arrows_counterclockwise: Frequens} is “Low”, the previous formula would only work if the days between {Start} and {End} is greater than 7. If it was less than 7, it would error. The following changes it so that it adds 2 days to {Last Shared} (which I think is what you had before). If none of the parameters match, it will also add 2 days. Happy to tweak if needed.

Apologies for the confusion!

IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days'), DATEADD({Last Shared}, 2, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 7, 'days')), DATEADD({Last Shared}, 2, 'days'))), 'YYYY-MM-DD'))

AnnicaT · ‎Jul 01, 2019

Thank you! Both for the fix and the explanaition of sharing bases and good practice there. Yes, it’s a copy of a base and I’ve stripped a bunch of tables, info etc, so no worries what happens to it in the futura. The original is safe.

There are two anomalies in the {BiU Share Status - Test Column} column.
I added a checkbox column called anomalies to easier be able to point them out.
The events have started, and are ongoing which should then be :ballot_box_with_check: , but they say :tickets:
It is ONLY supposed to say :tickets: if TODAY() is before {Start} (and {Tickets} is not ‘No’)

Edited to add: I can see that ‘ :link: ’ doesn’t show for those events that are within range and haven’t yet been shared / {Last Shared} (highlighted by the column {Not yet shared}). I need it to fill all eventualities and leave no blanks.

AlliAlosa · ‎Jul 02, 2019

Thank you Annica!

I think the following might work… apologies again for all the back and forth - this one is a bit complicated (as I’m sure you know :slightly_smiling_face: )

I rewrote the formula and I took a look at your base - I think some of the anomalies might be caused because there are differences between the {Next Share} field and the {Next Share - Test Column} field. The below formula as-is returned “ :link: ”, but when I changed {Next Share} to {Next Share - Test Column}, it returned “ :ballot_box_with_check: ” as expected.

The below formula will also return “ :link: ” for anything that has not yet been shared.

IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", "🔗"), "🎟"), IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))), "-"), "🔗")

AnnicaT · ‎Jul 02, 2019

No need for apologies, We’re far beyond the point of what I could achieve on my own. I am beyond greatful for all the help I’m getting.

I added one more column, {BiU Share Status - Latest Test Column} and changed to {Next Share - Test Column} like you said in the testbase. There is only one thing left now that I can see after manually checking each event. One event, that I renamed ‘ :exclamation: Random event name 25 :exclamation: ’, so it stands out from the others, should return a ‘ :hourglass_flowing_sand: ’, but now returns a ‘ :link: ’. All other are accurate.

Yes, that is because I like to test things in a new column before applying “permanently”. I know I’m not all that advanced in my skills, so I like to have the freedom to test around and not loose any step until I’ve actually gotten things to work. Maybe it’s a bit of a weird quirk. :slightly_smiling_face: