Different dates for Status RYGG



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


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/15/22 Answer ✓

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!