Skip to main content

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?

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

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


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?

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?


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!

Reply


Terms & Conditions