"IF AND" Statement w/ Single-Select Field

[Katie_Hill]

Hello! I’m trying to write a “Deadline Alert” formula to indicate that a particular task is :rotating_light: PAST DUE​:rotating_light:or :negative_squared_cross_mark: which means on track / not overdue. The conditions for a past due task are as follows:

(1) The field “Task Status” (Single Select Field) must NOT be set to “Complete” or “Not Started”---- completed or not started tasks can’t be past due :winking_face:
(2) The field “Approval Deadline” must be TRUE aka not blank
(3) The field “Days Past Due” (which already has a formula in it to calculate days past the approval deadline using DATETIME_DIFF) must be <1 (a negative number).

Below is my poor attempt that has not been working. I’m new to formulas and greatly appreciate the help!

IF(AND(
{Approval Deadline}=TRUE,
{Days Past Due}=<1,
{Task Status}!=(“Complete”,“Not Started”)
),
” :rotating_light: PAST DUE​:rotating_light:",
“ :negative_squared_cross_mark: ”)

[W_Vann_Hall]

Try

IF(
    AND(
        {Approval Deadline},
        NOT(
            OR(
                {Task Status}='Complete',
                {Task Status}='Not Started'
                )
            ),
        {Days Past Due}<1
        ),
    '🚨',
    '❎'
    )

That will seemingly show a flashing light if today is the deadline — {Days Past Due} = 0 — so you may need to change the last comparison to {Days Past Due}<0, depending on the formula for {Days Past Due}.

[Katie_Hill]

This worked! Thank you very much @W_Vann_Hall