Gray Bubble if item expired before 31 December 2022

I have the name of some "items" in one column and the expiry dates of the items in another column. I want the status symbol to show Gray if the expiry date will be on or before 31st December 2022.

Can anyone help with this formulla?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Vinton Douglas

    =IF([Expiry Date]@row<=DATE(2022,12,31),"Gray")

    will work for the request in your post. This will also show Gray if the date field is blank. Is that ok? If not, use this

    =IF(AND(ISDATE([Expiry Date]@row),[Expiry Date]@row<=DATE(2022,12,31)),"Gray")

    If you wanted the formula to be dynamic with the current year, you could do this

    =IF([Expiry Date]@row<=DATE(YEAR(TODAY()),12,31),"Gray")

    I am assuming you are using more colors than Gray, so if you have an existing IF statement, remember the order of the IFs make a difference - this IF may need to be near the front of the nest. Also remember that you cannot have a formula sometimes and manual entry other times in the same column. Your formula will be overwritten and will not cascade down the column.

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Vinton Douglas

    =IF([Expiry Date]@row<=DATE(2022,12,31),"Gray")

    will work for the request in your post. This will also show Gray if the date field is blank. Is that ok? If not, use this

    =IF(AND(ISDATE([Expiry Date]@row),[Expiry Date]@row<=DATE(2022,12,31)),"Gray")

    If you wanted the formula to be dynamic with the current year, you could do this

    =IF([Expiry Date]@row<=DATE(YEAR(TODAY()),12,31),"Gray")

    I am assuming you are using more colors than Gray, so if you have an existing IF statement, remember the order of the IFs make a difference - this IF may need to be near the front of the nest. Also remember that you cannot have a formula sometimes and manual entry other times in the same column. Your formula will be overwritten and will not cascade down the column.

    cheers

    Kelly

  • Works Great. Thanks a lot

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!