Need help combining these 2 IF statements

Options

I can get both of these to work separately, but when I try combining them, using IF(OR or IF(AND or even just another IF(, the "Yellow" part won't work. Suggestions, please?

=IF([Milestone Due Date]@row > ([Today Helper]@row + 30), "Green", IF([Milestone Due Date]@row > ([Today Helper]@row + 7), "Yellow", "Red"))

=IF([Milestone Completed Date]@row > [Milestone Due Date]@row, "Red", IF([Milestone Completed Date]@row < [Milestone Due Date]@row, "Green"))

Thank you,

Ashley

Tags:

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Ashley McAdoo,

    Something like this?

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF([Milestone Due Date]@row > TODAY(7), "Yellow", "Red"))

    Sample output:

    If your want the +7/+30 to be yellow/green respectively, change the formula slightly:

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row >= TODAY(30)), "Green", IF([Milestone Due Date]@row >= TODAY(7), "Yellow", "Red"))

    For:

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/14/24 Answer ✓
    Options

    @Ashley McAdoo

    Further investigating shows that in both cases @Nick Korna forgot to account for when the completed date is Both after the due date and less then 7 days. Its all due to how the And() function works in the first portion of the formula. His corrected formula is .

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < TODAY(7)), "Red", "Yellow"))

    If your trying to bypass the issues with the today formula then do

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > [Today Helper]@row+30), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < [Today Helper]@row+7), "Red", "Yellow"))

    Below is the outcome of both versions of the formula.

    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.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    Options

    @Ashley McAdoo

    To be sure I got it. You want it to flag red if the completed date is over or equal to 30 days the due date. Yellow if up to 7 days over the due date. And green if before the due date?

    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.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Ashley McAdoo,

    Something like this?

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF([Milestone Due Date]@row > TODAY(7), "Yellow", "Red"))

    Sample output:

    If your want the +7/+30 to be yellow/green respectively, change the formula slightly:

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row >= TODAY(30)), "Green", IF([Milestone Due Date]@row >= TODAY(7), "Yellow", "Red"))

    For:

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭
    Options

    @Mark.poole,

    I would like it to turn red if the due date is within 7 days or if the completion date is after the due date; green if the due date is more then 30 days out or if the completion date is before the due date, and yellow if the due date is between 7 and 30 days from today.

    Thanks,

    Ashley

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/14/24
    Options

    @Ashley McAdoo, @Nick Korna

    Based on my test Nick's Solution did not quite work the way you wanted but was very close. IT also looks like she was trying to implement a work around for how the Today() function works, From what I could tell mine does exactly what you want. Nick if you could shorten the formula for Ashley for a better solution I welcome it as it would be a learning experience for me as well.

    =IF(ISBLANK([Milestone Completed Date]@row), IF([Milestone Due Date]@row > [Today Helper]@row + 30, "Green", IF(AND([Milestone Due Date]@row > [Today Helper]@row + 7, [Milestone Due Date]@row < [Today Helper]@row + 30), "Yellow", IF([Milestone Due Date]@row <= [Today Helper]@row + 7, "Red"))), IF([Milestone Completed Date]@row > [Milestone Due Date]@row, "Red", "Green"))

    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.

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/14/24 Answer ✓
    Options

    @Ashley McAdoo

    Further investigating shows that in both cases @Nick Korna forgot to account for when the completed date is Both after the due date and less then 7 days. Its all due to how the And() function works in the first portion of the formula. His corrected formula is .

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < TODAY(7)), "Red", "Yellow"))

    If your trying to bypass the issues with the today formula then do

    =IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > [Today Helper]@row+30), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < [Today Helper]@row+7), "Red", "Yellow"))

    Below is the outcome of both versions of the formula.

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!