RYGB Due Date/Completed

Options

Hello all,

Looking to create a formula using RYGB based on Due Date/Completed.

Red - Past due date and not completed

Yellow - Within 2 days of due date and not completed

Green - Date Completed ON/OR prior to Due Date

Blue - Date Completed Past Due Date



Best Answers

  • Sean Urbin
    Answer ✓
    Options

    Thank you for the help to the both of you!

    Mark, yours was working, but the RYGB column is showing a red mark when there is no date filled in at all. What would need to be added to nothing appears if the Due Date is empty?

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Give this a try

    =IF(ISBLANK([Complete Date]@row), IF([Due Date]@row > TODAY(), "Red", IF([Due Date]@row >= TODAY() - 2, "Yellow", "Green")), IF([Complete Date]@row > [Due Date]@row, "Blue", "Green"))

  • Sean Urbin
    Answer ✓
    Options

    Thank you for the help to the both of you!

    Mark, yours was working, but the RYGB column is showing a red mark when there is no date filled in at all. What would need to be added to nothing appears if the Due Date is empty?

  • Sean Urbin
    Options

    Figured it out. Just needed to add the following into your formula Mark.

    =IF(ISBLANK([Due Date]@row), "", IF(AND([Due Date]@row < TODAY(), ISBLANK([Date Completed]@row)), "Red", IF(AND([Due Date]@row <= TODAY(2), ISBLANK([Date Completed]@row)), "Yellow", IF([Date Completed]@row <= [Due Date]@row, "Green", IF([Date Completed]@row > [Due Date]@row, "Blue", IF(ISBLANK([Due Date]@row), ""))))))

    Thank you again for your help! Mich appreciated!

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Options

    Whoops! Thought I had all the conditions covered, but missed that obvious one -- good catch, Sean!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!