# Task Health - Red, Yellow, Green

✭✭
edited 08/15/23

I am currently using =IF(TODAY() - [Due Date]@row < 0, "Green", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() - [Due Date]@row < [Processing Goal]@row, "Yellow"))) because I can't figure out how to get the yellow to work correctly.

I am looking for a formula that will display green if today is less than the due date, red if today is greater than the due date and yellow if today + 1/2 the processing goal is less than the due date. If the processing goal is 10 days, I want the health to turn yellow when we are 5 days from the due date so we can assign additional resources before we reach the due date. Can anyone help?

• ✭✭✭✭✭✭

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

• ✭✭✭✭✭✭

Hello @blmccue!

Try the following formula:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY([Processing Goal]@row / 2) < [Due Date]@row, "Green", "Yellow"))

If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

### Monique Odom-Stearn

Pronouns: She/Her (What’s this?)

“Take chances, make mistakes, get messy!” – Ms. Frizzle

• ✭✭
edited 08/16/23

Hi @Monique_Odom_Comcast, I had tried that once and tried again but receive and invalid data type error.

• ✭✭
edited 08/17/23

Wondering if anyone else has any suggestions?

I've also added a helper column that calculates 1/2 the processing goal to see if that would help fix the errors

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

• ✭✭✭✭✭✭

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

• ✭✭✭✭✭✭

Hi @blmccue,

The issue may be in trying to use a decimal inside TODAY(), that causes an "#invalid data type" error.

Hope that helps,

Dave

• ✭✭

@Paul Newcome, that worked!

Greatly appreciated.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!