Status based on Actual VS Targeted Dates

Options

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

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



Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Best Answer

  • che.rabajante
    che.rabajante ✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • che.rabajante
    che.rabajante ✭✭✭✭✭
    Answer ✓
    Options

    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

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @che.rabajante ,

    That works perfectly! Thanks so much for taking the time to help me solve this problem. It is greatly appreciated!

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!