
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 05:59 AM
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.
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJul 08, 2019 10:38 AM
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}), "β³", "π")))), "β")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 06:37 AM
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})), "π"))), "-")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 06:55 AM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 07:01 AM
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})), "π")))), "-")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 07:09 AM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 07:53 AM
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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 08:01 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 08:05 AM
Yay! Youβre so welcome! Happy to help :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 08:32 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
βJun 24, 2019 09:00 AM
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}), "βοΈ", "π"), "π")), "-")
