RYG Status w/ Date and Checkbox

I am trying to get my RYG Status column to auto update based on a couple of factors - I can seem to get them all to align.

If RFP Due Date is Blank = Gray

If RFP Due Date is in the future = Green

If RFP Due Date is within 3 days of today = Yellow (this one is not working)

If RFP Due Date is today or in the past and the RFP Submitted is not checked = Red (this one is not working)

If RFP Submitted is checked = Green

Here is my current formula:

=IF(ISBLANK([RFP Due Date]@row), "Gray", IF([RFP Submitted]@row = 1, "Green", IF([RFP Due Date]@row <= TODAY(3), "Yellow", IF([RFP Due Date]@row <= TODAY(), "Red", IF([RFP Due Date]@row > TODAY(), "Green")))))



Best Answer

  • DKazatsky
    DKazatsky ✭✭✭
    Answer ✓

    Hi @Adrienne Strong

    Try this one: =IF(NOT(ISBLANK([Submitted Date]@row)), "Green", IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row > TODAY(), "Green")))))

    -Dave

Answers

  • DKazatsky
    DKazatsky ✭✭✭
    edited 01/30/23

    Hi @Adrienne Strong ,

    In the section where you are checking if the date is within 3 days, it is also matching for dates that anytime in the past, that is way it appears that the "red" condition is not working. Adding an "And" should fix it.

    Try this: =IF(ISBLANK([RFP Due Date]@row), "Gray", IF([RFP Submitted]@row = 1, "Green", IF(AND([RFP Due Date]@row >= TODAY(), [RFP Due Date]@row <= TODAY(3)), "Yellow", IF([RFP Due Date]@row <= TODAY(), "Red", IF([RFP Due Date]@row > TODAY(), "Green")))))

    Thanks,

    Dave

  • @DKazatsky ,

    We've changed up the structure of how we want this sheet to function. We've removed the Submitted checkbox column and replaced it with a Submitted Date column.

    If Due Date is Blank = Gray

    If Due Date is in the future = Green

    If Due Date is within 3 days of today = Yellow

    If Due Date is today or in the past = Red

    If Submitted Column has a date entered = Green (this one is not working)

    =IF(ISBLANK([Due Date]@row), "Gray", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Submitted Date]@row <= TODAY(), "Green")))))

    I'm not sure if one of the other formulas is overriding the Submitted Date? I've tried this in different orders.


    Thanks,

    Adrienne

  • DKazatsky
    DKazatsky ✭✭✭
    Answer ✓

    Hi @Adrienne Strong

    Try this one: =IF(NOT(ISBLANK([Submitted Date]@row)), "Green", IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row > TODAY(), "Green")))))

    -Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!