# Forumla Help, Nested IFs

Options
✭✭✭
edited 12/09/19

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!!

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Excellent! Glad you found a working solution.

• ✭✭✭✭✭✭
Options

I would make your row calls consistent...