Formulas | Green, Yellow, Red Health via Due Date

Sarah J Huibregtse · 03/01/21

Howdy all!

Long time researcher, first-time hair puller-outer :-) I'm hoping to create an automated RBY Health Column in my sheet based on the due date. I tried modifying some formulas based on what I've seen in other threads, but I'm really struggling and hoping to get some help from this community :-)

Long and short, I'd like to have a formula where ...

If the Due date is greater than 57 days, the Health is Green.
If the Due date is between 7 and 3 days, the Health is Yellow.
If the Due date is between 3 and 0 days (or in the past), the Health is Red.

Anyone able to help me out a bit? Appreciate any tips or assistance!

Bradley

P.S. As an aside, any particular videos y'all would recommend to review and build out?

Mark Cronk · 03/01/21

=IF([Due Date]@row -Today() >57, "Green", IF([Due Date]@row -Today() >4, "Yellow", "Red"))

Mark

Darin Wagner · 03/01/21

Bradley,

I'll take a first shot at it and my first post on the community...might be a better solution, but I think I have a formula that will work for you.

I have a column named Due Date [Column Set to Date] and in another column this formula: [Column Set to RYG Balls]

=IF([Due Date]@row > TODAY(57), "Green", IF(AND([Due Date]1 >= TODAY(3), [Due Date]1 <= TODAY(7)), "Yellow", IF([Due Date]1 < TODAY(3), "Red")))

If the Due date is greater than 57 days, the Health is Green.
- >Today(57)
If the Due date is between 7 and 3 days, the Health is Yellow.
- IF(AND for the between 3 and 7 days
If the Due date is between 3 and 0 days (or in the past), the Health is Red.
- < Today(3)

I hope that helps!

Thanks!

Mark Cronk · 03/01/21

=IF([Due Date]@row -Today() >57, "Green", IF([Due Date]@row -Today() >4, "Yellow", "Red"))

Mark

Sarah J Huibregtse · 03/01/21

@Mark; thank you, thank you, thank you!!! That worked perfectly -- of course, minus my accidentally "57" click. Appreciate you helping me out!

Darin Wagner · 03/01/21

Bradley,

I'll take a first shot at it and my first post on the community...might be a better solution, but I think I have a formula that will work for you.

I have a column named Due Date [Column Set to Date] and in another column this formula: [Column Set to RYG Balls]

=IF([Due Date]@row > TODAY(57), "Green", IF(AND([Due Date]1 >= TODAY(3), [Due Date]1 <= TODAY(7)), "Yellow", IF([Due Date]1 < TODAY(3), "Red")))

If the Due date is greater than 57 days, the Health is Green.
- >Today(57)
If the Due date is between 7 and 3 days, the Health is Yellow.
- IF(AND for the between 3 and 7 days
If the Due date is between 3 and 0 days (or in the past), the Health is Red.
- < Today(3)

I hope that helps!

Thanks!

Mark Cronk · 03/01/21

Glad you found a solution. Thank you for contributing to the Community.

Mark

Sarah J Huibregtse · 03/02/21

https://community.smartsheet.com/discussion/comment/281701#Comment_281701

Hey @Mark Cronk ! Sorry to bother you again, but I had a question regarding the formula. I modified some of the dates (and added a section of IF([Due Date]@row -Today() = 4, "Yellow", ... ), but I'm curious the close parenthesis behind "Today" in each of the color coded areas. Could you tell me what would go in those areas, or is it just a different way to classify the number of work days remaining?

Thanks!

Mark Cronk · 03/03/21

Hi Brad,

TODAY() is the expression for today's date. More information below:

https://help.smartsheet.com/function/today

Mark

Harry Wagner · 04/15/21

@Mark Cronk Could you help me with a formula. I would like to change the Status column (RYG) based on a date range and 4 other columns in the same row that are checked. Here are the two formulas have and need to combine the some way. Thanks.

=IF(TODAY() - [Next Annual Inspection Due]@row > -15, "Red", IF(TODAY() - [Next Annual Inspection Due]@row > -35, "Yellow", "Green"))

=IF(COUNTIFS([On Rent]@row:[Wire Rope Inspection]@row, =0) > 0, "Red", "Green")

Mark Cronk · 04/16/21

Hi @Harry Wagner ,

Try:

=IF(OR(TODAY() - [Next Annual Inspection Due]@row > -15, COUNTIF([On Rent]@row:[Wire Rope Inspection]@row, 0) > 0), "Red", IF(TODAY() - [Next Annual Inspection Due]@row > -35, "Yellow", "Green"))

Work?

Mark

Harry Wagner · 04/27/21

Perfect thank you.

Harry Wagner · 04/27/21

What do know about VLOOKUP in the same sheet?

Mark Cronk · 04/27/21

You can use VLOOKUP in the same sheet. The range you select must contain the value being looked for in the left most column.

Mark

Andrée Starå · 04/28/21

Hi @Harry Wagner

I hope you're well and safe!

To add to Mark's excellent advice/answer.

https://help.smartsheet.com/function/vlookup

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Harry Wagner · 04/28/21

Good Morning,

Here is what I am trying to do and not sure if it is possible. We have an equipment inspection sheet (Below) I keep track of On Rent Inspection and Off Rent inspections. I am trying to build a formula that will Put the Off rent date in the column based on the equipment number on the same row as the on rent date. Thanks for the help i just can't seem to figure it out.

Formulas | Green, Yellow, Red Health via Due Date

Best Answers

Answers

Help Article Resources

Categories