Nested Formula based off dates

AoifeBrennan
AoifeBrennan ✭
edited 05/24/24 in Formulas and Functions

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:
· Share on FacebookShare on Twitter

Answers

  • Mark.poole
    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”)))

    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.

    · Share on FacebookShare on Twitter
  • AoifeBrennan
    AoifeBrennan ✭

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

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/437649#Comment_437649

    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.

    · Share on FacebookShare on Twitter
  • AoifeBrennan
    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.

    Screenshot 2024-05-28 at 17.16.45.png

    · Share on FacebookShare on Twitter
  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    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

    · Share on FacebookShare on Twitter
  • Mark.poole
    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")))

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!