Help with Formula

Please help! I've been struggling with this for a while.

I'm trying to get a formula to return "Yes" or "No" based on several variables.

If the start date is less than today and status is 0-Backlog, Yes

End date is less than today + 30 and status is not equal to 4-Closed, Yes

Start date is less than today + 30 and status is 0-Backlog, Yes

Start date is less than today and Status is not equal to 4-Closed, Yes

Start date is less than today and Status is not equal to 5-Cancelled, Yes

Start date is less than today and Status is not equal to -1-Hold, Yes

Status is = to 1-Initiate, 2-Plan or 3-Execute, Yes

Otherwise, No

=IF(OR(AND([Start date]358 < TODAY(), Status358 = "0-Backlog")), (AND([End date]358 < TODAY(30), Status358 <> "4-Closed")), (AND([Start date]358 < TODAY(30), Status358 = "0-Backlog")), (AND([Start date]358 < TODAY(), Status358 <> "4-Closed")), (AND([Start date]358 < TODAY(), Status358 <> "5-Cancelled")), (AND([Start date]358 < TODAY(), Status358 <> "-1-Hold")), Status358 = "1-Initiate",

Status358 = "2-Plan", Status358 = "3-Execute"), "Yes", "No")

This returns #UNPARSEABLE.

This value works somewhat but still returns "Yes" if the Status is 5-Cancelled or 4-Closed or -1-Hold.

=IF(OR((AND([Start date]347 < TODAY(), Status347 = "0-Backlog")), (AND([End date]347 < TODAY(30), Status347 <> "4-Closed")), Status347 = "1-Initiate", Status347 = "2-Plan", Status347 = "3-Execute", (AND([Start date]347 < TODAY(30), Status347 = "0-Backlog")), (AND([Start date]347 < TODAY(0), Status347 <> "4-Closed", Status347 <> "5-Cancelled", Status347 <> "-1-Hold"))), "Yes", "No")

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What are all of the different Statuses possible?


    I noticed that you have two entries that overlap. The way they are written, the first is covered by the second which makes it redundant. Did you mean something different, or can we ignore the first?

    Start Date less than today and Status equals "0-Backlog"

    Start Date less than today + 30 and Status equals "0-Backlog"

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sorry yes you can ignore the first one.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. And what are all of the different Statuses?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • -1-Hold

    0-Backlog

    1-Initiate

    2-Plan

    3-Execute

    4-Closed

    5-Cancelled

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this one a go. It covers everything exactly as you have listed (minus the first one).


    =IF(OR(AND([Start Date]@row< TODAY(), Status@row <> "-1-Hold", Status@row <> "4-Closed", Status@row <> "5-Cancelled"), AND([Start Date]@row< TODAY(30), Status@row = "0-Backlog"), AND([End Date]@row< TODAY(30), Status@row <> "4-Closed"), Status@row = "1-Initiate", Status@row = "2-Plan", Status@row = "3-Execute"), "Yes", "No")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • It is working for all of them except it is still returning Yes for 5-Cancelled. Thanks for all your help!

  • I think it is returning Yes for Cancelled if the start date is earlier than Today.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Please verify. Also double check that I entered the "specific text" correctly within the formula. The BOLD portion below is where it should be EXCLUDING "5-Cancelled" with a Start Date of earlier than today. It may be because I missed a space somewhere such as before or after the hyphen as it is looking for an exact match on that string.

    =IF(OR(AND([Start Date]@row < TODAY(), Status@row <> "-1-Hold", Status@row <> "4-Closed", Status@row <> "5-Cancelled"), AND([Start Date]@row< TODAY(30), Status@row = "0-Backlog"), AND([End Date]@row< TODAY(30), Status@row <> "4-Closed"), Status@row = "1-Initiate", Status@row = "2-Plan", Status@row = "3-Execute"), "Yes", "No")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I did some more testing on this. This returns Yes if the [Start Date] is earlier than TODAY and the status is "5-Cancelled". If I change the [Start Date] to after TODAY and the status is "5-Cancelled", it returns no. I'd like to return No if status is "5-Cancelled", regardless of Start Date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!