Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Why does my nested formula work for some cells in the column but not all?

#INVALID OPERATION is the error message I receive for some but not all cells.

=IF(AND([Created By Date]@row < TODAY(-365), [Target Completion Date]@row < TODAY()), "Red", IF(AND([Target Completion Date]@row >= TODAY(), ISBLANK([Project Status]@row)), "Yellow", IF([Project Status]@row = "Complete", "Green", IF([Target Completion Date]@row = 0, "Gray"))))

Answers

  • Community Champion

    Can you share examples of which cells it's not working for? Formula looks pretty robust to me, other than I would use "" instead of 0 in the last part ahead of "Gray", ie IF([Target Completion Date]@row = 0, "Gray")))) but that doesn't stop it working

    Rich @ Prodactive

    Rich Coles

    Prodactive | Smartsheet-aligned Platinum partners

    Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions