Requesting help with conditional IF formula for makeshift status field

[AnnicaT]

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]

AlliAlosa:

IF({Last Shared}, IF({BiU} = “Yes”, IF(IS_AFTER(TODAY(), {End Date}), “ :white_check_mark: ”, IF(IS_BEFORE(TODAY(), {Start}), IF(FIND(“No”,{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, ‘days’)), “ :hourglass_flowing_sand: ”, “ :link: ”), “ :tickets: ”), IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),“ :ballot_box_with_check: ”, “ :link: ”)))), “-”), “ :link: ”)

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.

[AlliAlosa]

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.

AnnicaT:

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:

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, "🎟")))), "➖"), "🔗")

[AnnicaT]

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.

[AlliAlosa]

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}), "⏳", "🎟")))), "➖"), "🔗")

[AnnicaT]

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!

[AlliAlosa]

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:

[AnnicaT]

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?

[AlliAlosa]

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!

[AnnicaT]

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:

[AlliAlosa · Accepted solution]

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}), "⏳", "🎟")))), "➖")