# 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

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?

• ✭✭✭✭✭✭
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"))

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?

• 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!

• ✭✭✭✭✭
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!