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.

[AlliAlosa]

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

[AnnicaT]

@AlliAlosa

AlliAlosa:

IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), “ :white_check_mark: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Last Shared})), “ :ballot_box_with_check: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Last Shared})), “ :link: ”))), “-”)

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?

[AlliAlosa]

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

[AnnicaT]

@AlliAlosa

AlliAlosa:

IF({BiU}, IF({Next Shared}, IF(IS_AFTER(TODAY(), {End Date}), “ :white_check_mark: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Next Shared})), “ :ballot_box_with_check: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Next Shared})), “ :link: ”)))), “-”)

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?

[AlliAlosa]

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:

[AnnicaT]

AlliAlosa:

IF({BiU}, IF({Next Share}, IF(IS_AFTER(TODAY(), {End Date}), “ :white_check_mark: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE(TODAY(), {Next Share})), “ :ballot_box_with_check: ”, IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_AFTER(TODAY(), {Next Share})), “ :link: ”))), “ :link: ”), “-”)

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.

[AlliAlosa]

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

[AnnicaT]

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

[AlliAlosa]

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}), "☑️", "🔗"), "🔗")), "-")