RYG Status w/ Date and Checkbox

Adrienne Strong

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

DKazatsky

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

DKazatsky

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

Adrienne Strong

@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

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

Adrienne Strong

Thank you!