Help

Requesting help with conditional IF formula for makeshift status field

Topic Labels: Formulas
Solved
Jump to Solution
5355 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:

AnnicaT
7 - App Architect
7 - App Architect

I tested this formula on our “Live” base and noticed a few issues that I think are down to me being to up in formula and ending up being unclear. I’m so sorry this all has ended up far more complicated than a simple answer and solved.

:link: - If an event starts in the next three days OR if TODAY is on or after {Next Share} (Regardless of what value {Tickets} have).

:ballot_box_with_check: - If an event has started {Start} but TODAY() is before {End Date}
(Regardless of what value {Tickets} have).

:white_check_mark: - If TODAY() is after {End Date}
(Regardless of what value {Tickets} have).

:tickets: - If you can book tickets (so {Tickets}=‘any other value then No’) AND TODAY() is before {Start}

:heavy_minus_sign: - If {BiU}=‘No’

I hope I was clear and that it helps you understand what I’m after.

Don’t be sorry! This is a complicated one to solve and without knowing much about the use case it makes it a bit more difficult.

Not a weird quirk - I totally do that too! :slightly_smiling_face: Just wanted to make sure we were both testing the formula against the same dates.

Your latest post was very clear - however it doesn’t take into account the “ :hourglass_flowing_sand: ” from the previous variations - let me know if that needs to be worked back in. The below will also return “ :link: ” if {Last Shared} is empty. I went through and checked each one and it looks like each result matches the parameters you laid out in your last post.

IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(), {Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}) = 0, "🎟")))), ""), "🔗")

OMG - my brain.

:hourglass_flowing_sand: - if an event has {Tickets}=‘No’ and TODAY() is more than 3 days before {Start}

How could I forget. And I was trying to collect my thoughts and be clear. I knew I was confusing myself, but that’s extreme.

Hahaha no worries at all!

So I added a new column to your practice base, {BiU Share Status - Latest and Greatest}, with the below formula. Fingers crossed that this is the winner! There were a couple places where I thought it might be wrong as it’s returning a :link: instead of :hourglass_flowing_sand: as it did in previous iterations, but then I realized that’s because the {Next Share} date for those records is today.

IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(),{Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}), "", "🎟")))), ""), "🔗")

There are three instances where the {Ticket}=‘No’ and TODAY() is way more than three days before {Start} and should then be ‘ :hourglass_flowing_sand: ’, but show ‘ :link: ’. I’ve marked the instances in the column “Should be :hourglass_flowing_sand: ”.

If we can get that to work I’ll be ecstatic. I’ve spotted another thing in all this, but I’ll deal with that on a rainy day, or month (Grins). These are the functions that are needed now and what matters most.
You have no idea how insanely gratful I am for your invaluable help. Thank you!

We’ll get this figured out! I think we’re just having some miscommunication on the dates.

For the three records you checked, the first two are showing :link: because TODAY() is after {Next Share}. The last one is showing :link: because {Last Shared} is blank.

Let me know what should change :slightly_smiling_face:

I missed that two had a share date, which they shouldn’t yet so that’s strange, but oh well; I removed them. But anyway. Those three events checked should all show “ :hourglass_flowing_sand: ” because it’s more than three days until they start and there is no registration or pre-sale of tickets. When I removed the value in {Last Shared} they should have switched over to “ :hourglass_flowing_sand: ”, but didn’t.

Ok. I’ll try to be clear and not confuse myself so I end up confusing you as well. If I can achieve that then today is a good day. I’ll type out my rainy day, or month, plan as well so I have it clear in my head already now, but PLEASE don’t feel like you have to trouble yourself with it.

:heavy_minus_sign: = Event should not be shared at all (is not {BiU}=‘Yes’)

:hourglass_flowing_sand: = Not time to share yet. The event is more than three days away and there is no registration or pre-sale of tickets.

(Down the line I also want this to show if the event has {Tickets=‘value other then No’} and TODAY() is before {Tickets released}, but that truly is something for a rainy day, unless you truly feel like tackling that one as well. I sooo don’t expect you to.)

:tickets: = If you can book tickets / reserve seat and TODAY() is before {Start}. (This one is also one for a rainy day, see above as it should only show if TODAY is after {Tickets released}).

:link: = Share event. Two uses

  1. The event has not yet been shared ({Last Shared}=BLANK()) and the {Start} of the event is either three days away or TODAY().

  2. Today() is on or after {Next Share}.

:ballot_box_with_check: = The event has been shared ({Last Shared}), is still ongoing (after {Start}, but before {End Date}) and TODAY() is before {Next Share}.

:white_check_mark: = TODAY() is after {End Date}

I think I might have managed, but you have to be the judge of that. Clear? Clear-ish?

Hi Annica!

Apologies for the delay in getting back to you… things have been crazy lately!

Thank you so much for the clear explanations. It definitely helps a ton. I want to make sure I have this right before giving you yet another formula that doesn’t work :frowning: I’m starting to feel like I’m not helping, lol.

No Share :heavy_minus_sign:

After Event :white_check_mark:

During Event
To Be Shared :link:
Shared :ballot_box_with_check:

More than 3 Days Before Event
No Tickets :hourglass_flowing_sand:
Tickets :tickets:

Less than 3 Days Before Event
To Be Shared :link:
Tickets :tickets:

If all that looks correct, I’ll get to work!

That looks correct to me. Below is just me trying to be super clear to avoid confusion.

During event
:ballot_box_with_check: IF {Last Shared} AND before {Next Share}

Less than 3 Days Before Event
To Be Shared :link:
Tickets :tickets:

**On day of event / {Start} **
To Be Shared :link:
Tickets :link:

Excellent - thank you again!

I’m very anxious to see if this works. I added another column to your base - {BiU Share Status - Newest}. I checked and it looks like all the parameters are met… let me know :slightly_smiling_face:

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