Status based on Actual VS Targeted Dates

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!!

The formula and all cells referenced are on the SAME sheet
The formula is being written in the "Completion Status" column (screenshot 2)
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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

che.rabajante

Hello @Sherry Fox

Please try this

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.