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

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

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

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

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


If TODAY() is more than 3 days before {Start} and the value(-s) in {Tickets} IS ‘No’ i want it to show and if less than 3 days .

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

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

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

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

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 “ ”?

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.

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


					"", 


					""


				), 


			""


		)


))), "-")

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.

Oh, and I have a column that cleans up the dateformat of {Next Share} if that matters.


Can you do that already IN {Next Share}?

IF({Next Share (formula)}, DATETIME_FORMAT({Next Share (formula)}, 'YYYY-MM-DD'))

IF({Next Share (formula)}, DATETIME_FORMAT({Next Share (formula)}, 'YYYY-MM-DD'))

Hi there,

Thank you so much!! This helped a lot. I think the following should work, it’s a bit complicated and hard to configure without seeing the base itself so let me know if any part doesn’t perform as it should.

IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), "", IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "", IF(IS_BEFORE(TODAY(), {Start}), "", IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"", "")))),"")), "-")

To answer your latest question about {Next Share}, yes, you can certainly do that within the formula. The following should do the trick (I also worked in the additional clause you said you were going to add):

IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({ Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 2, 'days')))), 'YYYY-MM-DD'))

For some reason all results are now “-”

This one works almost perfectly except that I have two occurances of ERROR!

Both share the same base They are one day events that has {Start} and {End Date} 2019/07/07.


{Last Shared} 2019/07/01


{ Frequens}=‘Low’


{Tickets}=‘No’

Unfortunately I can’t share the base here which would have helped a lot.

OMG! Massive face palm] I doublechecked and have of course used English in one place and no wonder it didn’t work. Corrected it and now works. Will double check in a sec to see that everything works there.

The ERROR! though is not a translation error.

There are some anomalies. Working on scrubbing base of telling data and translating so I can share with you and you can have a look around.

How does one share a base (in the forum)?

Just by link or?

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.