I'm trying to create a nested IF formula and am stumped. Please help!

Parker103

My columns are: Finish ... which is a date field, Status ... which can be Complete, Not Started or In Progress, and a column where I want to insert a Green, Yellow, or Red symbol based on logic.

My needs: If the finish date is in the past and the status is not 'complete' I want to insert a RED symbol. If the finish date with within 5 days of today's date and the status is not 'complete' I want to insert a Yellow symbol. Otherwise symbol should be inserted as Green.

My stab at a formula:

=IF(AND(TODAY() > [Finish]@row, [Status]@row <> "Complete", "Red"), IF[Finish]@row < TODAY(+5), "Yellow"), "Green")

Any advice?

Katie G

As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))

Julie Fortney

Hi @Parker103

Try this:

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(Finish@row < TODAY(+5), "Yellow", "Green"))

All I did was change around some parentheses. You were close!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Zachary Hall

Here's what I would recommend.

=IF([Status]@row = "Complete", "Green", IF(TODAY()>Finish]@row, "Red" , IF([Finish]@row<TODAY(5) , "Yellow" , "Green"))

Hope it helps!

Katie G

As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))

Julie Fortney

Yes @Katie G , that is a great point!

Parker103

Thanks VERY much for the help. Formula is working great :)