Need help combining these 2 IF statements

Ashley McAdoo

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

Nick Korna

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

@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.

Mark.poole

@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?

Nick Korna

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

@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

@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"))

Mark.poole

@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.