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

Angela Ryer

=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!

Paul Newcome

You would need to ensure that your working days in your account settings are set to Mon - Fri. You can also change the -2 to -3 and see how that works.

My apologies. I fat fingered the formula. It should be >= -2. Somehow I missed that =.

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

Angela Ryer

=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

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

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

Angela Ryer

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.

Angela Ryer

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

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)

Angela Ryer

@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

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

Angela Ryer

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?

Paul Newcome

You would need to ensure that your working days in your account settings are set to Mon - Fri. You can also change the -2 to -3 and see how that works.

My apologies. I fat fingered the formula. It should be >= -2. Somehow I missed that =.

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

Angela Ryer

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

Paul Newcome

Happy to help! 👍️