Formulas | Green, Yellow, Red Health via Due Date

Sarah J Huibregtse

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

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

Mark

Darin Wagner

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

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

Mark

Sarah J Huibregtse

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

Darin Wagner

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

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

Mark

Sarah J Huibregtse

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

Hi Brad,

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

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

Mark

Harry Wagner

@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

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

Perfect thank you.

Harry Wagner

What do know about VLOOKUP in the same sheet?

Mark Cronk

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å

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

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.