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.
Best Answer
-
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?
Answers
-
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", ""))))))))
-
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?
-
This did the trick!!! Thanks so much!!!! 😁
-
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"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!