Formula, IF Statement?? Escalation Status

Sherry Fox

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"

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

https://community.smartsheet.com/discussion/comment/420431#Comment_420431

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?

heyjay

=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

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

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

@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 "-"

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

@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.

heyjay

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

@heyjay,

Yes, final paragraph is correct.

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","-")))