Different dates for Status RYGG

Hi!

I have been pulling my hair out over this. I have gotten each part to work independently, but not together. We are trying to get the status to change if two separate dates are past due, or 30 days from coming due. We also have a column that would select the gray status if it is selected. Below is what I had written. Any advice/suggestions would be greatly appreciated!

=IF(AND([COI Expiration]@row - TODAY() > 30), "Green", IF([COI Expiration]@row < TODAY()), "Red", "Yellow", IF(AND([Contract Expiration]@row - TODAY() > 30), "Green", IF([Contract Expiration]@row < TODAY()), "Red", "Yellow", IF(AND([Do Not Use - Safety Concern]@row = 1), "Gray")))

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @bmclark

Can you confirm that your columns are set to Date Type of columns?

[COI Expiration] and [Contract Expiration] will both need to be Date Type columns in order for the formula to use the TODAY function.

If they are set to Date, try each statement individually to see where the error may be occurring:

=IF([Do Not Use - Safety Concern]@row = 1, "Gray")

=IF(AND(([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green")

=IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red")

All comments

Gia Thinh

Hi bmclark,

Try this:

=IF([Do Not Use - Safety Concern]@row = 1, "Gray", IF(AND( ([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green", IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red", "Yellow")))

bmclark

It is still coming up as UNPARSEABLE.

bmclark

Apologies, it came up as invalid operation, not unparseable.

Genevieve P.

Hi @bmclark

Can you confirm that your columns are set to Date Type of columns?

[COI Expiration] and [Contract Expiration] will both need to be Date Type columns in order for the formula to use the TODAY function.

If they are set to Date, try each statement individually to see where the error may be occurring:

=IF([Do Not Use - Safety Concern]@row = 1, "Gray")

=IF(AND(([COI Expiration]@row - TODAY()) > 30, ([Contract Expiration]@row - TODAY()) > 30), "Green")

=IF(AND([COI Expiration]@row < TODAY(), [Contract Expiration]@row < TODAY()), "Red")

bmclark

That was the issue. Thanks for your help!

Genevieve P.

No problem 🙂

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.