Nested Formula based off dates

Options

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

Tags:

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @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”)))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • AoifeBrennan
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • AoifeBrennan
    Options

    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
    Brian_Richardson Overachievers
    Options

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!