Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Requesting help with conditional IF formula for makeshift status field

Topic Labels: Formulas
Solved
Jump to Solution
12577 44
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnicaT
7 - App Architect
7 - App Architect

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.

44 Replies 44

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

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

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.

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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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

Just by link or?

0d41b7239ab01c5f5e8f76a7f7d714f76ba86a36.png

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.

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

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.

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}),"☑️", "🔗")))), "-"), "🔗")

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: