IF Formula - If Multiple Select Field = x, then status = "y"

[Collin_Thompson]

I’m pretty new at this whole IF formula thing, can you help me out? In the multiple select field are each of the tasks that need to be completed for the project.

I want to indicate in the “status” field whether the project is complete or overdue, based on the date the project is due and whether or not all of the tasks are completed. Something along the lines of this article, but having the formula read the multiple select field instead of a checkbox: https://support.airtable.com/hc/en-us/articles/221104988-Color-coding-records-in-Airtable#ifformula

https://airtable.com/shr19aK239f3cMAyu/tblCnIfGu8tlF0rHA

[Chester_McLaugh]

I’m not clear what constitutes a completed task. Is it when Visibility Status is empty?

Could you write out your if statement in conversational English using the actual field names?

Something like:

If Visibility Due is in the past and Visibility Status is empty, then make Status "Complete"
If Visibility Due is in the past and Visibility Status is not empty, then make Status "Overdue"
otherwise if Visibility Status is not empty, make status “In Progress”

[Collin_Thompson]

Thanks for getting back to me, sorry about the confusion. In conversational english, it would essentially be,

if Visibility Due is in the past, and all Visibility Status options are selected, then make status 'complete’
If Visibility Due is in the past, and not all Visibility Status options are selected, then make status "Overdue"
otherwise if not all Visibility Status options are selected, make status "In Progress"
if all Visibility Status options are selected, make status “complete”

I’m assuming I’d probably have to write the name of each Visibility Status option in the IF formula, maybe not. But I guess this was my original question. Is the way I’m envisioning it with the multiple selection field possible?

[Chester_McLaugh]

This should work for you.

Unfortunately, the COUNT function only seems to work on linked records, not multiple selects so rather than counting the total number of items, we have to search for each one individually to ensure they’re all present.

IF(
	AND(
		FIND("Detached Event Description",{Visibility Status}),
		FIND("External/Internal Contact Info",{Visibility Status})
	),
	"Complete",
	IF(
		IS_AFTER(TODAY(),{Visibility Due}),
		"Overdue",
		"In Progress"
	)
)

[Collin_Thompson]

Worked like a charm! Thank you!