RYGB Due Date/Completed

Sean Urbin

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

Mark Safran

Hi Sean, this should work:

=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"))))

-MS

Sean Urbin

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?

L_123

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

Mark Safran

Hi Sean, this should work:

=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"))))

-MS

Sean Urbin

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

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

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