Formula error

jgneely72151

I'm not getting the result I need in the Overall Status field.

Row 2 should be "compliant"

Row 3 and 4 are correct, "cancelled"

Row 5 should be "partially compliant"

Row 6 is correct "not compliant"

Row 7 is correct "compliant" because checkboxes 2 and 3 are checked and date is in the future.

Row 8 should be "not compliant" because date is in the past. I'm getting compliant no matter what date I enter or even if it is blank.

Are my arguements incorrect? Do I have my arguments in the wrong place?

Here's what I want to happen:

1. If program status is revoked or deferred, then return cancel (regardless of what's checked)
2. If all 3 checkboxes are checked, then compliant
3. if any 2 boxes checked, then partially compliant
4. If only 1 box checked, then not compliant
5. If checkbox 2 and 3 are checked AND date field is in the past, "Not compliant" but if date is in the future, "Compliant"

By the way, there will only be dates for part time fields and program status will have other status but if it is revoked or deferred, then cancel.

I really want it to work because i need to prioritize when to start oboarding

Here is the formula I used:

=IF(OR([Program Status]@row = "Revoked", [Program Status]@row = "Deferred"), "Cancelled", IF(AND([Student Health]@row = 1, Advantage@row = 1, [FT PT]@row = "Part-time"), "Compliant", IF([Start Onboarding]@row < TODAY(), "Not Compliant", IF(COUNTIF([SON Compliance]@row :Advantage@row , 1) = 3, "Compliant", IF(COUNTIF([SON Compliance]@row :Advantage@row , 1) = 2, "Partially Compliant", "Not Compliant")))))

Any guidance is greatly appreciated!

jmyzk_cloudsmart_jp

Hi @jgneely72151

You seem to have an additional condition for the "Part-Time" student, in addtion to the above 1-5 condtions.

Here is what I interpreted your condition.

IF Program Status is "Revoked" or "Deferred"

    → "Cancelled"


ELSE IF

    FT PT is "Part-time"

    AND [Student Health] and [Advantage] are checked

    AND [SON Compliance] is not checked

    → THEN check Start Onboarding date

        → If the date is valid AND in the future or today → "Compliant"

        → If the date is valid BUT in the past → "Not Compliant"

        → If the date is blank or not a valid date → "Not Compliant"


ELSE

    → Use checkbox count logic:

        - If 3 boxes checked → "Compliant"

        - If 2 boxes checked → "Partially Compliant"

        - If 1 or 0 boxes checked → "Not Compliant"

https://app.smartsheet.com/b/publish?EQBCT=14afd8a45a5847168b45eb2c0af93274

=IF(OR([Program Status]@row = "Revoked", [Program Status]@row = "Deferred"), "Cancelled", IF(AND([FT PT]@row = "Part-time", [Student Health]@row = 1, Advantage@row = 1, [SON Compliance]@row <> 1), IF(ISDATE([Start Onboarding]@row ), IF([Start Onboarding]@row >= TODAY(), "Compliant", "Not Compliant"), "Not Compliant"), IF(COUNTIF([SON Compliance]@row :Advantage@row , 1) = 3, "Compliant", IF(COUNTIF([SON Compliance]@row :Advantage@row , 1) = 2, "Partially Compliant", "Not Compliant"))))

or

=IF(OR([Program Status]@row = "Revoked", [Program Status]@row = "Deferred"),

    "Cancelled",

    IF(AND([FT PT]@row = "Part-time", [Student Health]@row = 1, Advantage@row = 1, [SON Compliance]@row <> 1),

        IF(ISDATE([Start Onboarding]@row),

            IF([Start Onboarding]@row >= TODAY(), "Compliant", "Not Compliant"),

            "Not Compliant"

        ),

        IF(COUNTIF([SON Compliance]@row:Advantage@row, 1) = 3, "Compliant",

            IF(COUNTIF([SON Compliance]@row:Advantage@row, 1) = 2, "Partially Compliant", "Not Compliant")

        )

    )

)