Status based on Actual VS Targeted Dates

Sherry Fox

I need to have a formula for my Completion Status column. Here are the criteria and the screenshots. I keep failing at trying to write this formula, so I would appreciate some assistance. thanks in advance for your help!!

1. The formula and all cells referenced are on the SAME sheet
2. The formula is being written in the "Completion Status" column (screenshot 2)
3. There are 3 potential Statuses for this column:

On-time - Completed prior to "Target Implementation Date" (screenshot 1). "Status" (screenshot 2) column is marked as Complete when a date is entered into "Actual Implementation Complete Date" (screenshot 1) is before "Target Implementation Date" (screenshot 1).

Complete Past Due - "Actual Implementation Complete Date" is after Target Implementation Date.

Past Due - If the "Status" column does NOT show as "Complete" yet ("Actual Implementation Complete Date" is blank), and the Target Implementation Date has passed.

In Progress - And all others should show the in progress status. So that would be 4 statuses altogether.

che.rabajante

Hello @Sherry Fox

Please try this

=IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Date]@row), "On-time", IF(AND(Status@row = "Open", NOT(ISDATE([Actual Implementation Complete Date]@row)), [Target Implementation Date]@row < TODAY()), "Past Due", IF(AND(Status@row = "Open", NOT(ISDATE([Actual Implementation Complete Date]@row)), [Target Implementation Date]@row >= TODAY()), "In Progress", IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row > [Target Implementation Date]@row), "Completed Past Due"))))

Hope this helps!

che

che.rabajante

Hello @Sherry Fox

Please try this

=IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Date]@row), "On-time", IF(AND(Status@row = "Open", NOT(ISDATE([Actual Implementation Complete Date]@row)), [Target Implementation Date]@row < TODAY()), "Past Due", IF(AND(Status@row = "Open", NOT(ISDATE([Actual Implementation Complete Date]@row)), [Target Implementation Date]@row >= TODAY()), "In Progress", IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row > [Target Implementation Date]@row), "Completed Past Due"))))

Hope this helps!

che

Sherry Fox

@che.rabajante ,

That works perfectly! Thanks so much for taking the time to help me solve this problem. It is greatly appreciated!