Is there a way to change a status cell based on multiple date criteria?

Neela_15

I am looking for help get the below equation to behave like I want. Currently it gives me #INVALID OPERATION, which I have trouble shot and determined it is giving me this because it is trying to perform logical operations on null values. The formula works if I populate all of the below date columns, but that isn’t how the business process works. So I think the solution revolves around only checking the date columns that are populated, but can’t seem to figure out the syntax. I looked at using automation to change the “Status” column based on these dates, but automation does not allow for change a symbol column. (See attached for Screenshot)

Business Case: Change the status column (symbol) based on the below criteria. The “Status” column should be either green, red, or blue based on the latest date that appears in one of the below columns (date order below is based in the order they are inputted over time).

           AIR-611 Due Date—Should be green unless today is greater than the AIR-611 Due Date (turn red). This is the first date column to be populated.

           AIR-601 Due Date—Should be green unless today is greater than the AIR-601 Due Date (turn red). This is the second date column to be populated.

           AIR-600 Due Date—Should be green unless today is greater than the AIR-600 Due Date (turn red). This is the third date column to be populated.

           Sent to AIR-360—Should be blue when a date is entered indicating “complete.” This is the last column to be populated.

Pseudo Code: If the “SPM Approval Workflow Initiated checkbox is unchecked, The “Status” column is empty; otherwise, if TODAY() is greater than “AIR-611 Due Date” or“AIR-601 Due Date” or “AIR-600 Due Date,” then change “Status” column to red symbol; however, if the “Sent to AIR-360” row is not blank, change “Status” column to blue; otherwise “Status” column is green if none of these conditions are met.

=IF([SPM Approval Workflow

Initiated]@row = 0, "",

IF(TODAY() > [AIR-611 Due Date]@row, "Red", IF(AND(NOT(ISBLANK([AIR-601 Due Date]@row)), TODAY() > [AIR-601 Due Date]@row), "Red", IF(AND(NOT(ISBLANK([AIR-600 Due Date]@row)), TODAY() > [AIR-600 Due Date]@row), "Red", IF(AND(NOT(ISBLANK([Sent to AIR-360]@row)), TODAY() = [Sent to AIR-360]@row), "Blue", "Green")))))

Changing Status Symbol Based on Dates.pdf

Kelly Moore

Hey @Neela_15

See if this works for you. I moved your Blue response to the beginning - I wasn't sure if that blue response would trump any of the reds. If the Reds are the higher priority then shift the Blue IF to where you originally had it in the equation. I also added the 'pseudo code' provision of the Status being blank if the other column is unchecked. Be sure to check that I named that column correctly.

IF([SPM Approval Workflow]@row=0, "", IF(AND(ISDATE([Sent to AIR-360]@row), TODAY() = [Sent to AIR-360]@row), "Blue", IF(OR(AND(TODAY() > [AIR-611 Due Date]@row, ISDATE([AIR-611 Due Date]@row)), AND(ISDATE([AIR-601 Due Date]@row), TODAY() > [AIR-601 Due Date]@row), AND(ISDATE([AIR-600 Due Date]@row), TODAY() > [AIR-600 Due Date]@row)), "Red", "Green"))) 

Does it work?

Kelly