Gray Bubble if item expired before 31 December 2022

Vinton Douglas

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?

Kelly Moore

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

Kelly Moore

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

Vinton Douglas

Works Great. Thanks a lot