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

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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([email protected] = "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.

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

  • 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([email protected] = "Open", [email protected] = "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 NewcomePaul 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([email protected] = "Open", [email protected] = "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 NewcomePaul Newcome ✭✭✭✭✭

    =IF(OR(AND(OR([email protected] = "Open", [email protected] = "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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.