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({BiU}, IF(IS_AFTER(TODAY(), {End Date}), “ :white_check_mark: ”, IF({Next Share}, IF(IS_BEFORE(TODAY(), {Next Share}), “ :ballot_box_with_check: ”, “ :link: ”), “ :link: ”)), “-”)

That did it. And I double checked this time.
My {End Date} is set up to never be empty so Errors there.
Now on to study what you’ve done so I learn for myself.

Thank you!!!

[AnnicaT]

Hi again @AlliAlosa!
I’m sorry to ask again, but you gave such great and greatly appreciated help last time, that I hope you have time for a last tweak of your previous formula.

I have a multiple select field called {Tickets}.
If TODAY() is before {Start} and the value(-s) in {Tickets} is NOT ‘No’ i want it to show :tickets:
If TODAY() is more than 3 days before {Start} and the value(-s) in {Tickets} IS ‘No’ i want it to show :hourglass_flowing_sand: and if less than 3 days :link: .

How would I manage this?
I’ve managed to get one part working, but not the other. I’m misplacing my )'s as usual, but I can’t figure it out.

[AlliAlosa]

Hi there! Don’t be sorry to ask for help :slightly_smiling_face: I am more than happy to!

The following formula should do what you want! Did you want to incorporate a symbol for when the date is TODAY() or after TODAY()? I can certainly work that in if you want.

IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No", {Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", "🔗"), "🎟"))

One other thing to note… Be careful with the FIND() function and Multiple-select fields. If you have any other options that could appear in that field that include exactly the string “No”, (case sensitive, capital “N”, lowercase “o”), the above formula could produce some unexpected results.

For example, if one of your options is “Not Yet”, the formula will find the “No” in “Not Yet”.

[AnnicaT]

Hi!
Thank you for helping me.
I actually need the new part to be incorporated with the previous formula you helped me with. Is that possible? Like I said, I managed one, but not all parts on my own.

[AlliAlosa]

Aha! Guess I missed that part :slightly_smiling_face: Stay tuned!

[AlliAlosa]

I will need just a little bit more info to wrap this up :slightly_smiling_face:

How should the new part of the formula react to the fields {BiU}, {Next Share} and {End Date}? For example…

if TODAY() is before {Start},
{BiU} is not checked,
and {Tickets} does not contain “No”

Should the formula return “-” or “ :tickets: ”?

It would be super helpful if you could write out exactly what you need, like how you did for this part of the formula, but including the parameters of the first part of the formula as well. I hope that makes sense!

[AnnicaT]

So, this is what I did, and hopefully it makes sense as a guide of what I wish to achieve; if nothing else it will serve as comic relief smiles

IF({BiU}='Yes', 
IF(
	IS_AFTER(TODAY(), {End Date)}), 
	"✅", 
IF(
	AND( {Tickets}!='No', IS_BEFORE(TODAY(), {Start})),
	"🎟",
IF(
	AND( {Tickets}='No', DATETIME_DIFF(TODAY(),{Start}) > 3 ),
	"⏳",
		IF(
			{Next Share}, 
				IF(
					IS_BEFORE(TODAY(), {Next Share}), 
					"☑️", 
					"🔗"
				), 
			"🔗"
		)
))), "-")

I tried different placement versions and so on, but alas, no luck.

AlliAlosa:

if TODAY() is before {Start},
{BiU} is not checked,
and {Tickets} does not contain “No”

I would like it to return “-”
I only want it to return values other than “-” if {BiU}=‘Yes’

I hope I made sense now.

Note: I edited the post to be in ALL English.

[AnnicaT]

Oh, I work in a different language and translated the unique words in the formula, but missed one.
{Startdatum} = {Start}, not in a formula sense, but in a translation sense.

[AlliAlosa]

Thank you @AnnicaT! This is definitely helpful. Could I bother you to answer another question for me? What are your formulas for {Next Share} and {End Date}? Knowing those would be really helpful toward ordering the formula.

Thank you!

[AnnicaT]

So for {End Date} I use this one

IF(
{🔔 Last registration},
{🔔 Last registration},	
	IF({Start}!={End}, {End}, {Start}))

For {Next Share} I have this one

IF(
{Last Shared}=BLANK(),
'',		
	IF(
		AND( {🔄 Frequens} = 'Low', DATETIME_DIFF({End},{Start}) > 7 ),
		DATEADD({Last Shared}, 7, 'days'),
	IF(
		AND( {🔄 Frequens} = 'High', DATETIME_DIFF({End},{Start}) > 14 ),
		DATEADD({Last Shared}, 14, 'days'),
	IF(
		AND( {🔄 Frequens} = 'High', DATETIME_DIFF({End},{Start}) < 14 ),
		DATEADD({Last Shared}, 7, 'days'),
			DATEADD({Last Shared}, 2, 'days')
		)
	)
))

The second one, for {Next Share}, is next on my list to tweak, cause I need to add that if TODAY() is before {Start} and {Tickets}!=‘No’ it should DATADD {Last Shared} 21 days.