How to make the health column mark red if Actual End date> Today using the column formula

edited 06/20/23 in Formulas and Functions

I have health column where I am managing the status of completed, in progress and planned with Green, Yellow and blue color respectively. Whereas I also nested the IF AND in same column if the Actual End Date> today and the status is On-Hold then the health column should be automictically be red. This is the formula.

=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red", IF(Status@row = "Planned", "Blue", ""))))

But the formula isn't working for "IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red"" this part. Any suggestions @Paul Newcome @Genevieve P.


  • Hollie Green
    Hollie Green ✭✭✭✭✭

    Are you sure the greater than today is what you want to be Red. Only asking because I know I will type the incorrect greater than or less than symbol myself sometimes and it would make more sense for it to be Red if the date was less than today.

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭


    =IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Planned", "Blue", IF(AND(Status@row = "On-Hold", [Actual End Date]@row > TODAY()), "Red", ""))))

    However this does not assign a color for On-Hold with a date less than today.

  • Hey @Ray Lindstrom Thanks for guiding, this formula is working fine for In Progress, Planned and completed. But "if the actual date is beyond today and the task status is not completed then it should mark red". This condition i am unable to met. Kindly guide me for that segment.

  • Hey @Hollie Green yes if actual date is more than today and the status is not completed then want the health column to show red other than that all the status have defined color.

    Can you guide for same.

  • Hollie Green
    Hollie Green ✭✭✭✭✭
    edited 06/22/23

    @moumita77 Your formula looks correct to work for that can you provide a screenshot of your sheet with column headers and what it is doing for the "Red" part of your formula. I copied your formula exactly and it worked

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!