Skip to main content
I am trying to group line items based on the below conditions, but for some reason it's not working for all the "Done" ones. It does work for the 2023 items that are marked Done in the status column, but the one 2024 item that is marked Done in the status column still returns the 2024Q3. I really hope this makes sense.
 
IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",
IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",
IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",
IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",
IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",
IF(AND({Labels},"(2023)",{Status} = "Done"),"2023 Complete",
IF(AND({Labels},"(2024)",{Status} = "Done"),"2024 Complete",
"Unscheduled")))))))

Could you provide a screenshot of some example data and include what you would want the formula's output to be for each piece of data?

Thanks for the reply! First screenshot, it works as expected. But the 2nd one (same Airtable), it doesn't

The "2024Q3" in the purple column should be "2024 Complete" not "2024Q3".

Also, not sure if it matters but the Labels & Status are being pulled in from a Jira sync.

 

Thanks for the reply! First screenshot, it works as expected. But the 2nd one (same Airtable), it doesn't

The "2024Q3" in the purple column should be "2024 Complete" not "2024Q3".

Also, not sure if it matters but the Labels & Status are being pulled in from a Jira sync.

 


Hi,
it stops when it find true condition. so, labels with 2024Q.. cannot show 'Completed', regardless of Status.
Also, part with AND(  )   used in a wrong way.  It should be AND (exp1 , exp2 , exp3) and it's true when all three are true.

AND({Labels},"(2024)",{Status} = "Done")  means AND ( value of Labels_not_empty , strange_nonempty_string , Status=Done ).  when you have nonempty string, but you need boolean value, it just converts nonempty string to TRUE. So, any Labels where 2024Q.. not included and Status=Done will show '2023 Complete',
"'2024 Complete" cannot be shown.

Hi,
it stops when it find true condition. so, labels with 2024Q.. cannot show 'Completed', regardless of Status.
Also, part with AND(  )   used in a wrong way.  It should be AND (exp1 , exp2 , exp3) and it's true when all three are true.

AND({Labels},"(2024)",{Status} = "Done")  means AND ( value of Labels_not_empty , strange_nonempty_string , Status=Done ).  when you have nonempty string, but you need boolean value, it just converts nonempty string to TRUE. So, any Labels where 2024Q.. not included and Status=Done will show '2023 Complete',
"'2024 Complete" cannot be shown.


So basically what I want to do can't be done... 😞 

Thanks for the reply! First screenshot, it works as expected. But the 2nd one (same Airtable), it doesn't

The "2024Q3" in the purple column should be "2024 Complete" not "2024Q3".

Also, not sure if it matters but the Labels & Status are being pulled in from a Jira sync.

 


Hmm, yeah, I fixed just the 2023/2024 complete part:

 

IF(
AND(
FIND("2024", Labels),
{Status} = "Done"
),
"2024 Complete",

IF(
AND(
FIND("2023", Labels),
{Status} = "Done"
),
"2023 Complete"
)
)

 



To get around the "2024 Complete" thing not showing I swapped out the order of the conditionals as it seems like you want the latest year to be shown only
---
I don't think your "REGEX_MATCH" stuff works either actually.  I would highly recommend reviewing the formula field reference (https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference) as well as @Alexey_Gusev's notes on how your formula could be improved

 

Thank you so much! I am still learning how to use formulas so this is really helpful. 🙂 

So basically what I want to do can't be done... 😞 


No, you just have to change statements order (put Status check at start) and correct the part with AND.
I couldn't show correct formula yesterday, because to be honest I didn't understand what's your goal. 
But now I'm think I got it. 
In nested IFs, it's better to put 'guard clauses' at start.

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete") ,
...here you should put 'REGEX_MATCHes' in the same order as in your first formula..
..and put correct number of close brackets...

No, you just have to change statements order (put Status check at start) and correct the part with AND.
I couldn't show correct formula yesterday, because to be honest I didn't understand what's your goal. 
But now I'm think I got it. 
In nested IFs, it's better to put 'guard clauses' at start.

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete") ,
...here you should put 'REGEX_MATCHes' in the same order as in your first formula..
..and put correct number of close brackets...

Like this?

 

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete"),
IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",
IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",
IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",
IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",
IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",
"Unscheduled")))))))

Like this?

 

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete"),
IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",
IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",
IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",
IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",
IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",
"Unscheduled")))))))

Yes, looks good. 
The only problem is that you need to update the formula each 3 months, enter year 2025 etc.
But you can try to improve it, copy formula to the notepad and do Replace All   2024 => YEAR(TODAY())  , 2023 => (YEAR(TODAY())-1)
Of course it depends on what input data you expect in future, but I hope you understand the idea.

Yes, looks good. 
The only problem is that you need to update the formula each 3 months, enter year 2025 etc.
But you can try to improve it, copy formula to the notepad and do Replace All   2024 => YEAR(TODAY())  , 2023 => (YEAR(TODAY())-1)
Of course it depends on what input data you expect in future, but I hope you understand the idea.


Weird. When I paste that complete formula in, save it and confirm the change, ends up removing all except the Done formula. Maybe that's a glitch?

 

Weird. When I paste that complete formula in, save it and confirm the change, ends up removing all except the Done formula. Maybe that's a glitch?

 


Nope, just a bracket missed. Or, more accurately, misplaced.
I tried with the same result. Quite a rare behavior, I saw it 2 times 2-3 years ago, when I learned to write formulas in Airtable. 

That's a correct working version:

 

 

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete")),
IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",
IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",
IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",
IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",
IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",
"Unscheduled"))))))

 

Nope, just a bracket missed. Or, more accurately, misplaced.
I tried with the same result. Quite a rare behavior, I saw it 2 times 2-3 years ago, when I learned to write formulas in Airtable. 

That's a correct working version:

 

 

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete")),
IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",
IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",
IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",
IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",
IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",
"Unscheduled"))))))

 


That worked! Thank you sooooooo much!

Reply


Terms & Conditions