combine formulas (check flag based on date, OR if checkbox is checked)

=IF(Status6 = "Open", IF([Due Date]6 <= TODAY(2), 1, 0), (IF([Send to High Risk Report Now]6 = 1, 1, 0)))

I have this formula that each IF function works on its own, but i can't seem to get them to work together.

Objective: Check Flag if status is "Open" and due date is <=TODAY(2) -OR- check flag if the checkbox is checke

Bonus: If status can be "Open" or "On Hold" (and be either option) as part of the formula, great. But at minimum, at least "open".

I am not sure what i'm missing and i've tried a few routes and can't seem to get the two formulas combined into one.


-Thanks!

Best Answer

Answers

  • =IF([Send to High Risk Report Now]@row = 1, 1, IF(Status@row = "Open", IF([Due Date]@row <= TODAY(2), 1, 0)))

    Got the above formula to work - however, would like to have it be based on networkdays. so check flag if within 2 business days or equal to today. And only check if status "Open" OR "On Hold" - I can't seem to figure out adding those elements to the formula.

    -thanks in advance.

  • Alejandra
    Alejandra Employee

    Hi @Angela Ryer,

    Try this formula:

    =IF([Send to High Risk Report Now]@row = 1, 1, IF(AND(OR([Due Date]@row = "Open", [Due Date]@row = "On hold"), [Due Date]@row <= TODAY(2)), 1, 0))

  • Alejandra
    Alejandra Employee

    Also, if needed, more information on the functions I used can be found here: https://help.smartsheet.com/functions

  • The formula combination didn't work.The status would be "open" or "on hold" and the date be <=today(2) but still wouldn't check.

  • Ahh, i see, it said [Due Date] vs [Status] I updated those references

    =IF([Send to High Risk Report Now]@row = 1, 1, IF(AND(OR(Status@row = "Open", Status@row = "On hold"), [Due Date]@row <= TODAY(2)), 1, 0))

    Now it works for "open" or "on hold" but still based on days (vs networkdays) I've tried variations of adding NETWORKDAYS, but can't seem to get it working. I viewed the function list as well, but no luck.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here's another variation.

    I am still unsure though of exactly what the problem with the days is that you are referring to. What is it doing vs what is it supposed to be doing? If you are able to tell us exactly how you want it to work, we may be able to help with a tweak.


    =IF(OR(AND(OR(Status@row = "Open", Status@row = "On Hold"), [Due Date]@row<= TODAY(2)), [Send to High Risk Report Now]@row = 1), 1)

  • @Paul Newcome I want it off of business days. not counting (Sat & Sun) *(If I use the same idea in another sheet, for it to base it off the working days/holidays i've entered into that project settings. I've used NETWORKDAYS in harvey balls symbol column on other sheets, and it works fine. but i can't seem to get the function to work here.


    Example: if <=TODAY(2) and today is a Thurs. I want if the due date is the following Mon, it checks the flag. Right now, since it bases it on days (counting sat & sun) it won't check the flag. I assumed NETWORKDAYS was the function to use, perhaps it is something else.


    Thanks in advance.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF(OR(AND(OR(Status@row = "Open", Status@row = "On Hold"), NETWORKDAYS([Due Date]@row, TODAY()) > -2), [Send to High Risk Report Now]@row = 1), 1)

  • Thanks @Paul Newcome - today is Fri 24th and a due date of Mon 27th - it should check the flag. I've used your formula, but it only checks the flag if a due date of sunday 26th. When due date is mon 27th, it UNchecks.


    Am i doing something else wrong? a setting or something i'm missing. I thought NETWORKDAYS function didn't count weekends - is my assumption wrong?

  • Hallelujah! Thanks @Alejandra for helping with the multiple IF status, and @Paul Newcome for the networkdays. Is a big help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!