NEED HELP WITH FORMULA: Status Based on Target End Date and Actual End Date

I have a status column that I would like to automatically update based on the dates in the Target End Date and Actual End Date columns. The parameters are as follows:

If the Actual End Date is less than or equal to the Target End Date = Completed On Time
If the Actual End Date is greater than than the Target End Date = Completed Late
If there is no Target Start Date and no Actual End Date = Not Started
If the Target Start Date is greater than today's date = Not Started
If there is no Actual End Date, AND Target End Date is over 8 days away = On Track
If there is no Actual End Date, AND Target End Date is less than 8 days away = At Risk
If there is no Actual End Date, AND Target End Date is past today = Behind

This is the formula I've got so far. It's nearly there..

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(AND(ISBLANK([Actual End Date]@row), [Start Date]@row > TODAY()), "Not Started", IF(ISDATE([Actual End Date]@row), "Completed On Time", IF(AND([Target End Date]@row >= TODAY(8), ISBLANK([Actual End Date]@row)), "On Track", IF(AND([Target End Date]@row <= TODAY(8), [Target End Date]@row >= TODAY(), ISBLANK([Actual End Date]@row)), "At Risk", IF(AND([Target End Date]@row < TODAY(), ISBLANK([Actual End Date]@row)), "Behind", ""))))))

Most of this is working correctly except:

Tasks that should be "Not Started" are showing as "Behind"
I can't figure out the right formula for "Completed Late"

I would love a nudge to help get me over the finish line.

Find more posts tagged with

Accepted answers

Nick Korna

Hi @CrystalJ_Medtronic ,

Inputting some data to test:

I think this formula does what you're trying to accomplish:

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(AND(ISDATE([Actual End Date]@row), [Actual End Date]@row <= [Target End Date]@row), "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late", IF(OR(AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), ([Target Start Date]@row > TODAY())), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Target End Date]@row < TODAY()), "Behind", IF(AND(ISBLANK([Actual End Date]@row), ([Target End Date]@row >= TODAY(8))), "On Track", "At Risk"))))))

Do you want to give this a whirl and see if it fixes the issues you're having?

All comments

Julie Fortney

Good Morning,

Try this formula:

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(AND([Target End Date]@row >= TODAY(8), ISBLANK([Actual End Date]@row)), "On Track", IF(AND([Target End Date]@row <= TODAY(8), [Target End Date]@row >= TODAY(), ISBLANK([Actual End Date]@row)), "At Risk", IF(AND([Target End Date]@row < TODAY(), ISBLANK([Actual End Date]@row)), "Behind", IF(AND(ISBLANK([Actual End Date]@row), ISBLANK([Target Start Date]@row)), "Not Started", IF([Target Start Date]@row > TODAY(), "Not Started", IF([Actual End Date]@row <= [Target End Date]@row, "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late", ""))))))))

Nick Korna

Hi @CrystalJ_Medtronic ,

Inputting some data to test:

I think this formula does what you're trying to accomplish:

Do you want to give this a whirl and see if it fixes the issues you're having?

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/350533#Comment_350533

This did the trick!!! Thanks so much!!!! 😁

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/350524#Comment_350524

I appreciate the responses from this community.. Y'all are the BEST! This works for everything except tasks that have no dates and should show as "Not Started" instead show as "Behind"

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.