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

CrystalJ_Medtronic
CrystalJ_Medtronic ✭✭✭✭
edited 11/09/22 in Formulas and Functions

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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

  • Julie Fortney
    Julie Fortney Overachievers

    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
    Nick Korna ✭✭✭✭✭✭
    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?

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!