Formula missing 2 parenthesis

Sherry Fox

It showed 2 parenthesis were missing. I attempted to add them to the end, however I got an error that the syntax was incorrect. Screenshot of the error shown at the bottom.

I am having issues with this formula. It is showing EVERYTHING as overdue (previous version). It is possible that the "Actual Implementation Complete Date" column could be before OR after the Target Implementation Date (which is a formula). Either way everything should not display as Overdue.

=IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "No Escalation", IF(AND(status@row = "Complete", [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","-")))

Andrea Zenner

@Sherry Fox Not sure what is happening with your sheet. Your formula is correct. I recreated it and it works for me. The only difference is my status and target implementation dates are not calculated. Those calculated fields may be mucking with your formula, though then I would expect an error to be returned, not Over Due for everything.

Sorry I can't be more help, but wanted you to know your formula is correct.

Nic Larsen

Just a couple of observations - may or may not matter in grand scheme of what you're trying to achieve.

1) Do you need the AND statement here in the first argument? If the row is Complete, do you still need to evaluate the date or can it just return "no escalation"? Would it ever show complete, but you still need to escalate?

=IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "No Escalation",

2) Should this 2nd one be not equal to complete? Again, if its marked Complete, why look at the dates.

IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due"

Sherry Fox

@Nic Larsen ,

Thanks for responding. You are correct on BOTH counts (1 & 2). I attempted both formulas and got the same error for each (see screenshot below).

Formula 1

=IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Date]@row, "No Escalation"))

Formula 2

=IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Date]@row, "Over Due"))

BOTH provided the same error. Did I do something wrong??

Sherry Fox

@Andrea Zenner ,

Thanks for your assistance. My data requires some formulas. This formula resides on what is referred as an Entity sheet, there are about 20+. About 10+ columns of data are entered into the Master file and then "feed" into the Entity sheets via formulas. This way they all update based on any changes made by the Program Manager. The team then makes their own updates to each sheet. I had never considered that there would be issues as a result of whether a cell contained a formula or raw data. In my eyes, it should not matter. Thanks again.

Nic Larsen

The formulas I posted in my response were just copied from your original, I didn't make any changes. I just wanted to be sure it was clear what section I was talking about.

I revised now and see if this works:

=IF(status@row = "Complete", "No Escalation", IF(AND(status@row <> "Complete", [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

@Nic,

I had to add 2 parenthesis again top this formula:

=IF(status@row = "Complete", "No Escalation", IF(AND(status@row <> "Complete", [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","-")))

Nic Larsen

It's these arguments that are causing your error. What are you trying to say here?

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

Nothing. Does this matter at all??

Actual Implementation Complete Date - raw data cell

Target Implementation Date - Formula

Status - Formula

Nic Larsen

Can try it this way. It didn't error out in testing, but not sure it's giving the At Risk result you're looking for as I don't quite follow the last IF statement.

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

Sherry Fox

@Nic Larsen ,

You are my hero!!!! That works perfectly! Thanks so much for all your help. I really appreciate this!!!!