At Risk Formula Unparseable Error

Kayla Johnstone

I am trying to build a nested at risk formula based on the following triggers.

-End date is today and Status is not complete it will trigger

-There is not enough time between today and the end date for the duration period left. So, if duration minus % complete is 4 days. and the period of time between today and the end date is 2 days it will trigger.

This is where I am stuck at, but I know it isn't correct. I get an unparseable error when nested but each individual section is accepted when run individually.

=IF(OR(Status@row = "In Progress", Status@row = "Not Started"), 0, IF(AND([End Date]@row <= TODAY(), 1, 0))), IF(AND([End Date]@row - VALUE(LEFT(Duration@row, FIND("d", Duration@row) - 1)) < TODAY()), 1)

SmartSheet.JPG

Paul Newcome

My apologies. I was just correcting the syntax of what you already had established for criteria. I'm not sure I follow exactly, but see how this works for you...

=IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started"), OR([End Date]@row <= TODAY(), [End Date]@row - VALUE(LEFT(Duration@row, FIND("d", Duration@row) - 1)) < TODAY())), 1)

Paul Newcome

Give this a go...

=IF(OR(Status@row = "In Progress", Status@row = "Not Started"), 0, IF(OR([End Date]@row <= TODAY(), [End Date]@row - VALUE(LEFT(Duration@row, FIND("d", Duration@row) - 1)) < TODAY()), 1))

Kayla Johnstone

This is better-but, I think the first expression needs to change to something like If the End Date is today and the Status is In Progress or Not Started, 1

I just don't know how to get it to combine the two expressions.

Paul Newcome

My apologies. I was just correcting the syntax of what you already had established for criteria. I'm not sure I follow exactly, but see how this works for you...

=IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started"), OR([End Date]@row <= TODAY(), [End Date]@row - VALUE(LEFT(Duration@row, FIND("d", Duration@row) - 1)) < TODAY())), 1)

Kayla Johnstone

That worked! Thank you!

Paul Newcome

Excellent! Happy to help! 👍️