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

Find more posts tagged with

Accepted answers

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

All comments

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.

2023-02-10 15_30_50-(40) Sales Pipeline - Throughline _ by IIG - Smartsheet.jpg

Thanks,

Adrienne

DKazatsky

Hi @Adrienne Strong

-Dave

Adrienne Strong

Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.