Formula, IF Statement?? Escalation Status

Sherry Fox
Sherry Fox ✭✭✭✭✭✭
edited 02/19/24 in Formulas and Functions

I want to add a formula; The criteria is as follows:

IF "Actual Implementation Complete Date" = BLANK

AND "Target Implementation Date" is 14 (business days) (greater than or equal to) to

than "Escalation Status" = "At Risk", otherwise "Overdue"

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

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

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    =IF(AND(
    [Actual Implementation Complete Date]:[Actual Implementation Complete Date] = "",
    [Target Implementation Date]:[Target Implementation Date],14)
    ,"At Risk","Overdue")
    
    1. You put this under the Escalation Status Column
    2. 14 days - this needs to be compared to something. Is that GREATER THAN or EQUAL TO 14 days from Today()?

    ...

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    Yes, the if the Actual is blank, and the Target is 14 days past today, then yes. Now question, why is the entire column being references rather than @roiw for those 2 fields? I would have thought they all reference the @row. If not, what is the difference when they do and don't?

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • heyjay
    heyjay ✭✭✭✭✭
    edited 02/19/24
    =IF(AND(
    [Actual Implementation Complete Date]@row = "",
    [Target Implementation Date]@row, > Today(-14),
    [Target Implementation Date]@row, < Today(),
    ,"At Risk","Overdue")
    

    Yes you are right! Can you please try the fomula above.

    e: made some changes.

    ...

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    edited 02/20/24

    @heyjay ,

    The formula shows as #unparsable. Attached is a screenshot, and if you look at the end, you will see the final parenthesis is PINK rather than BLUE. I attempted at add the final parenthesis to the very end, however that is not where it is apparently needs to go. Even in Excel the "formula grammar" of where commas and parenthesis go was my biggest issue. So where does the final parenthesis go? Thanks so very much!!!!!


    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • heyjay
    heyjay ✭✭✭✭✭
    =IF(AND(
    [Target Implementation Date]@row = " ", 
    [Actual Implementation Complete Date]@row >= TODAY(-14), 
    [Actual Implementation Complete Date]@row < TODAY()), 
    "At Risk", "Over Due")
    

    ...

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @heyjay ,

    Well, yes and no. The formula DOES work as it is intended to. However, that is when we realized that we do not have enough criteria to make it work properly.

    Status - Will be Open if the Actual Implementation Completion Date is blank, and Closed when a date is entered.

    Escalation - Should actually focus on the Status column initially. with this as the criteria:

    ~ Status =

    "Complete" AND Actual Implementation Completion Date <= Target = "No Escalation"

    "Complete" AND Actual Implementation Completion Date => Target = "Over Due"

    "Open"

    "Open" AND "Target Implementation Date" is 14 (business days) (greater than or equal to) to

    than "Escalation Status" = "At Risk", otherwise "-"


    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • heyjay
    heyjay ✭✭✭✭✭

    You mentioned that the Status is either Open or Closed but the Escalation is looking for a Complete Status

    Status - Will be Open if the Actual Implementation Completion Date is blank, and Closed when a date is entered.

    Status = IF({Completion Date} = "", "Open", "Closed")
    


    ...

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @heyjay ,

    Yesterday was a VERY long day. My formula in the Status column is as follows:

    =IF([Actual Implementation Complete Date]@row = "", "Open", "Complete")

    The verbiage used was what the stakeholder wanted, despite I would noy have used it. Like you, I would have used open/closed.

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • heyjay
    heyjay ✭✭✭✭✭
    edited 02/21/24

    I hope you're having a great day today.

    
    =IF(AND(
    status@row = "Close", 
    [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row),
    "No Escalation",
    
    
    IF(AND(
    status@row = "Close", 
    [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row),
    "Over Due",
    
    
    IF(AND(
    status@row = "Open", 
    [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), 
    [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), 
    "At Risk",
    
    
    "-")
    


    Can you please check your last paragraph and see you can rephrase this for me,


    ...

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @heyjay,

    Yes, final paragraph is correct.

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    I t showed 2 parenthesis were missing. I attempted to add them to the end, however I got an error that the syntax was incorrect.

    =IF(AND(status@row = "Close", [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "No Escalation", IF(AND(status@row = "Close", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due", IF(AND(status@row = "Open", [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "At Risk","-")))


    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    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!