Forumla Help, Nested IFs
Hi, Looking to see if someone can help me with this formula. I would like it to follow the following logic:
- If Task is checked as complete=Β "Green"
- If Task is past complete by date and not checked as complete= "Red"
- If Task is 3 days until complete by date and not checked as complete ="Yellow"
Here is the what I have but it's not working.
=IF([Task Complete]@row, "Green", IF([Complete By]@row > TODAY(-3), "Yellow", IF([Complete By]@row >= TODAY(), "Red")))
Thank you!!
Comments
-
Try this slight modification...
There was no criterion for green. I am assuming its a checkbox based on your statement.
I would put Red next and use the Less than or equal to today. And then I would put yellow last and state Less than today + 3 days. You'll never see the equal two or less than today cause those will always fire Red. Everything else I am leaving blank.Β
=IF([Task Complete]@row = 1, "Green", IF([Complete By]@row <= TODAY(), "Red", IF([Complete By]@row <Today(3), "Yellow", "")))
Please let me know if this worked for you.Β
-
Hey Mike,
Unfortunately no,Β the Green worked when checked but it's doing yellow when its 3 days past the complete by date and all dates leading up to the complete by date is red.Β
-
Try switching the Red criteria from less than to greater than.
Β
IF([Complete By]@row <= TODAY(), "Red",Β
Β
switched toΒ
Β
IF([Complete By]@row >= TODAY(), "Red",Β
.
Same with the Yellow criteria.
Β
IF([Complete By]@row <Today(3), "Yellow",Β
Β
switched to
Β
IF([Complete By]@row <Today(3), "Yellow",Β
-
Hmm. Yes, Paul's suggestion should resolve that. I think I got my greater than, and less than's mixed up. The primary issue then was that the Green didn't have the criterion. Hope that gets it working for you!Β
-
Help Article Resources
Categories
Check out the Formula Handbook template!