Different dates for Status RYGG

bmclark

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

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

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 🙂