Need help combining these 2 IF statements
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
Best Answers
-
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!
-
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
-
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.
-
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!
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!