Forumla Help, Nested IFs

@SPark

Hi, Looking to see if someone can help me with this formula. I would like it to follow the following logic:

1. If Task is checked as complete= "Green"
2. If Task is past complete by date and not checked as complete= "Red"
3. 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!!

Mike Wilday

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. 

@SPark

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. 

Paul Newcome

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", 

Mike Wilday

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! 

@SPark

This is the formula I ended up using at it worked!! Thanks for everyone's help and input!! 

=IF([Task Complete]2 = 1, "Green", IF(AND([Complete By]@row > TODAY(), [Complete By]@row < TODAY(+3)), "Yellow", "Red"))

Paul Newcome

Excellent! Glad you found a working solution. 

Mike Wilday

I would make your row calls consistent... 

IF([Task Complete]@row= 1

 

=IF([Task Complete]@row = 1, "Green", IF(AND([Complete By]@row > TODAY(), [Complete By]@row < TODAY(+3)), "Yellow", "Red"))