Formula Help Needed - IF Statement w/ Multiple Conditions

JIM_AFO

Hi-

I'm attempting to return the "Green, Yellow, Red, Gray" colors with a column formula based off the Status and Planned Completion Date of my tasks.

Kelly Moore

Hey @JIM_AFO

I placed your status exclusion criteria at the beginning of the formula, which then simplified the date criteria at the end of the formula. I assumed that your screenshot which shows "Filed / Complete" means "Complete" If this is not true, change the wording of the textstring within quotes to reflect exactly the textstring in the Status column.

=IF(Status@row = "Filed / Complete", "Green", IF(Status@row = "N/A", "Gray", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(10)), "Red", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(21)), "Yellow"))))

Because Red color showed when no date was present, I added the condition that the Planned/Actual Completion Date had to be a date.

Does this work for you?

Kelly

Kelly Moore

Hey @JIM_AFO

I placed your status exclusion criteria at the beginning of the formula, which then simplified the date criteria at the end of the formula. I assumed that your screenshot which shows "Filed / Complete" means "Complete" If this is not true, change the wording of the textstring within quotes to reflect exactly the textstring in the Status column.

=IF(Status@row = "Filed / Complete", "Green", IF(Status@row = "N/A", "Gray", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(10)), "Red", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(21)), "Yellow"))))

Because Red color showed when no date was present, I added the condition that the Planned/Actual Completion Date had to be a date.

Does this work for you?

Kelly

JIM_AFO

Hi Kelly-

This worked perfectly. Thank you!