Help

Re: Requesting help with conditional IF formula for makeshift status field

Solved
Jump to Solution
2037 5
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.

1 Solution

Accepted Solutions

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

See Solution in Thread

44 Replies 44
AlliAlosa
10 - Mercury
10 - Mercury

Hi there! I believe the following should accomplish what you need :slightly_smiling_face:

IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), "", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Last Shared})), "☑️", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Last Shared})), "🔗"))), "-")

@AlliAlosa

Thank you so much!
That does indeed work. There was a tiny error in how it works, entiery by my own creation (It should check against {Next Shared} and not {Last shared} like I wrote in my post facepalm) that was super easily fixed.

I see now that I have a few instances where {Next Share} is empty and throws error messages. Where and how in the above formula do I add an IF to correct this?

You’re so welcome!

Not sure how you want your end result to be, but I’m assuming that even if there isn’t a date in {Next Shared}, you still want the result to be “-” if {BiU} is not checked?

If so, the below should work!

IF({BiU}, IF({Next Shared}, IF(IS_AFTER(TODAY(), {End Date}), "", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Next Shared})), "☑️", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Next Shared})), "🔗")))), "-")

@AlliAlosa

That takes care of the errors. Thank you!
Is it possible to get it to show :link: if {Next Share} is empty (instead of “-” in your latest formula) and “-” if {BiU} is empty?

Sure thing!

IF({BiU}, IF({Next Share}, IF(IS_AFTER(TODAY(), {End Date}), "", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Next Share})), "☑️", IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Next Share})), "🔗"))), "🔗"), "-")

One thing to note… if the formula in {Next Share} is not written to catch errors, the above formula won’t work. For example, say your formula for {Next Share} is…

DATEADD({Last Shared}, 1, 'week')

If {Last Shared} is empty, {Next Share} would return “#ERROR!”. In turn, the formula you’re working on now would also return “#ERROR!”, because it reads {Next Share} as having a value, but it can’t compare that value to anything.

To fix that, make sure your formula for {Next Share} (as well as {End Date}) is written to return a blank value if the field it depends on is empty. Using the above example…

IF({Last Shared}, DATEADD({Last Shared}, 1, 'week'))

I hope that all makes sense! :slightly_smiling_face:

Thank you, thank you, thank you! You’re a star!

Yes, that made sense and luckily the formula in those columns are solid and no further error are shown. Yay!

Thank you yet again for taking the time to help me. Now I just have to study a bit better what you’ve done in the different formulas so I can do it on my own in the future and not have to ask next time.

Yay! You’re so welcome! Happy to help :slightly_smiling_face:

@AlliAlosa

I think I cheered to soon.
The formula works, so I moved on to next thing on my to-do list, but now I see that it shows :link: for all fields that have {Next Share} empty. I only want this to happen if TODAY() is before {End Date}. If TODAY() is after {End Date} I wan’t it to show :white_check_mark: .

I’m sorry I didn’t catch that sooner and I fully understand if you do not have the time to help me further.

No problem at all! I was able to simplify the formula a little bit more too with that info. I think the following should work… I did get an #ERROR! if {End Date} is empty, but depending on your set-up that might not matter. Let me know if you need any other adjustments!

IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), "", IF({Next Share}, IF(IS_BEFORE(TODAY(), {Next Share}), "☑️", "🔗"), "🔗")), "-")

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!!!

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.

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”.

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.

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

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!

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.

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.

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.

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!

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.