Combining ISBLANK and TODAY formula for RYG Symbol Column

r0030
r0030 ✭✭✭✭
in Formulas and Functions

Hi all,


I'd like to automate the Health column in my Project Plan but am not sure how to go about doing this. The Health column is a symbol column (red, yellow, green). Ideally, I'd like for it to show the red symbol if the end date is 3 days late, yellow if the end date is 2 days late, green if we're on track, and blank if there is no end date in the end date column. The formula also needs to consider if the task has been completed (there is a status column which indicates if the associated task has not been started, is not applicable, is in progress, is complete, on hold, or cancelled). Could anybody help?


Screenshots below for reference:


Project Plan _ Smartsheet.png


Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓

    Here you go @rmc0030 :

    =IF(Status@row = "Complete", "", IF(ISBLANK([End Date]@row), "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", "")))))


    -MCS

    · Share on FacebookShare on Twitter

Answers

  • Mark Safran
    Mark Safran ✭✭✭✭✭

    Hi @rmc0030

    You'll want to do this with nested IF( ) statements. Try using this formula:

    =IF(Status@row = "Complete", "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", ""))))

    This assumes the following:

    • If the Status is 'Complete', Health is blank.
    • If Status is anything else, and the End Date is 3 or more days in the past, Health is Red
    • If Status is anything else, and the End Date is 1 or 2 days in the past, Health is Yellow
    • If Status is anything else, and the End Date is today or in the future, Health is Green


    If you can elaborate on how you want each of the other status values to be handled we can edit this further. But nested IF( ) statements is the way to go here.


    -MCS

    · Share on FacebookShare on Twitter
  • r0030
    r0030 ✭✭✭✭
    https://community.smartsheet.com/discussion/comment/335454#Comment_335454

    Thanks so much, Mark! This is super helpful. With the nested functions, how would I also add a statement to say that if the End Date column does not contain a date/is blank, also leave the Health column blank?

    · Share on FacebookShare on Twitter
  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓

    Here you go @rmc0030 :

    =IF(Status@row = "Complete", "", IF(ISBLANK([End Date]@row), "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", "")))))


    -MCS

    · Share on FacebookShare on Twitter
  • r0030
    r0030 ✭✭✭✭

    Thank you so much, @Mark Safran !

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!