Nested Formula based off dates

AoifeBrennan

Hi,

I am trying to make my sheet highlight rows using the Yes, Hold, NO RAG symbols when dates in specific columns are in the past or the row has not been approved. So far I have the below formula but its coming up as unparseable. Can anyone help? I cant see where I'm going wrong. If all dates are in the future it can be marked as Yes.

=IF(OR[Employers Liability Expiry Date]@row = <=TODAY(), “NO”,

IF(OR[Public Liability Expiry Date]@row = <=TODAY(), “NO”,

IF(OR[Product Liability Expiry Date]@row = <=TODAY(), “NO”,

IF(OR[Professional Indemnity Expiry Date]@row = <=TODAY(), “NO”,

IF(OR[Expiry Date 1]@row = <=TODAY(), “HOLD”,

IF(OR[Expiry Date 2]@row = <=TODAY(), “HOLD”,

IF(OR[Expiry Date 3]@row = <=TODAY(), “HOLD”,

IF(OR[Expiry Date 4]@row = <=TODAY(), “HOLD”,

IF(OR[Expiry Date 5]@row = <=TODAY(), “HOLD”,

IF(OR[Aoife Brennan Approval]@row = “Submitted”, “Hold”,

IF(OR[Aoife Brennan Approval]@row = “Declined”, “No”, ”Yes”)))))))))))

Mark.poole

@AoifeBrennan

I cleaned up the formula for you. See how it works out

=IF(OR([Employers Liability Expiry Date]@row <=TODAY(),
[Public Liability Expiry Date]@row <=TODAY(),
[Product Liability Expiry Date]@row <=TODAY(),
[Professional Indemnity Expiry Date]@row <=TODAY()), “NO”,
IF(OR([Expiry Date 1]@row <=TODAY(),
[Expiry Date 2]@row <=TODAY(),
[Expiry Date 3]@row <=TODAY(),
[Expiry Date 4]@row <=TODAY(),
[Expiry Date 5]@row <=TODAY(),
[Aoife Brennan Approval]@row = “Submitted”), “Hold”,
IF([Aoife Brennan Approval]@row = “Declined”, “No”, ”Yes”)))

AoifeBrennan

Hi Mark, thanks for commenting. It still doesn't seem to want to work. Getting unparsable still.

Mark.poole

https://community.smartsheet.com/discussion/comment/437649#Comment_437649

Could you show a screen shot of what your working with?

AoifeBrennan

I have a bunch of columns off to the right, some of which will flag when a date is past. What I am looking for is the supplier verification symbol column to change to hold if certain column dates are past and change to 'No' if the row hasn't been through approval or the specific insurance columns are past date if that makes sense.

Brian_Richardson

Unparseable means the formula isn’t typed out correctly, missing a back parenthesis is the most common problem. Marks formula looks right to me, but make sure when you copy/paste that all the column names turn colors. If not then one of the column names is wrong.

Mark.poole

https://community.smartsheet.com/discussion/comment/437992#Comment_437992

@AoifeBrennan

So weirdest thing. I created a mock up sheet to do the formula and I was getting the same problem. With out changing the formula at all. I added and built it one line at a time and it started working. The problem did not occur until the

[Aoife Brennan Approval]@row = “Submitted”), “Hold”,
IF([Aoife Brennan Approval]@row = “Declined”, “No”, ”Yes”)))

of the formula. I am not 100% sure anything changed with me backspacing and retyping. but here is yet again the formula once I got it to work on the mock up.

=IF(OR([Employers Liability Expiry Date]@row <= TODAY(), [Public Liability Expiry Date]@row <= TODAY(), [Product Liability Expiry Date]@row <= TODAY(), [Professional Indemnity Expiry Date]@row <= TODAY()), "No", IF(OR([Expiry Date 1]@row <= TODAY(), [Expiry Date 2]@row <= TODAY(), [Expiry Date 3]@row <= TODAY(), [Expiry Date 4]@row <= TODAY(), [Expiry Date 5]@row <= TODAY(), [Aoife Brennan Approval]@row = "Submitted"), "Hold", IF([Aoife Brennan Approval]@row = "Declined", "No", "Yes")))