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

CrystalJ_Medtronic

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.

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?

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:

=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?

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"