Conditional IF + Search formula?

[Katie_Wood]

I’m trying to create a formula that can search a column that contains multiple text dates and return only the earliest date.

My {Launching} column is a text field that contains many launch dates, as well as region codes for what the date applies to like this -
AU: 02/28/2021, CC: 03/01/2021, CK: 03/01/2021, CX: 03/01/2021, GU: 06/30/2021, MH: 06/30/2021, MP: 06/30/2021, NF: 02/28/2021, NZ: 02/28/2021, PR: 06/30/2021, TK: 02/28/2021, VI: 06/30/2021

I’d like to create a formula that can search this text field and tell me only the month of the earliest launch

My current formula is this:

IF(SEARCH(": 02/", {Launching}), “2Feb “,””)
& IF(SEARCH(": 03/", {Launching}), “3Mar “,””)
& IF(SEARCH(": 04/", {Launching}), “4Apr “,””)
& IF(SEARCH(": 05/", {Launching}), “5May “,””)
& IF(SEARCH(": 06/", {Launching}), “6Jun “,””)

The values returned look like this:

2Feb 3Mar
2Feb 3Mar 4Apr
2Feb 3Mar 4Apr
2Feb 3Mar 6Jun
2Feb 6Jun
3Mar

Is there a way to tell the formula to return the text value only for the first condition that is met?

[Katie_Wood · Accepted solution]

I managed to find the column on nested If statements and worked this out for myself. For anyone else who is curious the correct formula looks like this:

IF(SEARCH(": 02/", {Launching}), “2Feb”,
IF(SEARCH(": 03/", {Launching}), “3Mar”,
IF(SEARCH(": 04/", {Launching}), “4Apr”,
IF(SEARCH(": 05/", {Launching}), “5May”,
IF(SEARCH(": 06/", {Launching}), “6Jun”) ))))

And this topic helped me sort if out

[Gaspar_Caro]

I’m a beginner in writing Airtable formulas and have a similar use case as yours. I’m wondering why you didn’t use the OR() function instead of repeating SEARCH() in the Nested IF() statements? I’m trying to do it via OR() myself but most of my results are #ERROR! so I suspect that you didn’t use it for a similar reason?

[Katie_Wood]

In this case, the reason I didn’t use OR is because I needed to search the text field and return only one value. With OR it would return every value that it found that matched, where as I was only interested in finding the FIRST value that returned a result. This formula searches for February, if no February date is found it searches for March, if no March it searches for April, and so on. Hope that helps!