Jul 29, 2024 02:42 PM - edited Jul 29, 2024 02:58 PM
Jul 29, 2024 08:23 PM
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?
Jul 30, 2024 05:42 AM - edited Jul 30, 2024 05:54 AM
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.
Jul 30, 2024 03:21 PM
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.
Jul 30, 2024 05:45 PM
So basically what I want to do can't be done... 😞
Jul 30, 2024 08:17 PM
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
Jul 31, 2024 05:50 AM
Thank you so much! I am still learning how to use formulas so this is really helpful. 🙂
Jul 31, 2024 03:23 PM
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...
Jul 31, 2024 03:39 PM
Like this?
Aug 01, 2024 03:46 AM
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.