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