# Formula, IF Statement?? Escalation Status

Options
✭✭✭✭✭✭
edited 02/19/24

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

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

Tags:

• ✭✭✭✭✭
Options
=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()?

...

• ✭✭✭✭✭✭
Options

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

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭
edited 02/19/24
Options
=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.

...

• ✭✭✭✭✭✭
edited 02/20/24
Options

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

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

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

...

• ✭✭✭✭✭✭
Options

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

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭
Options

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

...

• ✭✭✭✭✭✭
Options

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

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭
edited 02/21/24
Options

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,

...

• ✭✭✭✭✭✭
Options

@heyjay,

Yes, final paragraph is correct.

Sherry Fox

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭✭
Options

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