# Formula skipping rows

Options
✭✭✭

Hi all,

I'm trying to use the below function to automatically change the color of Harvey balls to red, yellow, or green based on the length of time till a task's due date.

=IF([Due Date]@row >= TODAY(+61), "Green", IF([Due Date]@row >= TODAY(=31), "Yellow", "Red"))

However, for some reason some rows show "#invalid operation" rather than a colored ball.

I've reached out to support but haven't received any direction on how to adjust my formula. So any help or direction would be greatly appreciated.

Thank you!

Tags:

• ✭✭✭✭✭✭
Options

Try this...

=IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red")))

• ✭✭✭✭✭✭
Options

You would use something like this...

=IF(Standing@row = "Complete", "Blue", IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red"))))

• ✭✭✭✭✭✭
Options

Try this...

=IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red")))

• ✭✭✭
Options

Thank you, Paul! You're a rock-star. :)

• ✭✭✭
Options

@Paul Newcome , what would I add if I wanted anything marked as "complete" in a column labeled "standing" to show as blue?

I've tried playing around with it myself, but no luck.

• ✭✭✭✭✭✭
Options

You would use something like this...

=IF(Standing@row = "Complete", "Blue", IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red"))))

• ✭✭✭
Options

Thank you so much, Paul! Greatly appreciate your help. :)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!