How do I make a column formula for parent/child RYGB balls?

I need a formula to do the following for the Child row (note: I do have a Child Count Column set up):

Contract Expiration Date is in the past = Red

Contract Expiration Date is in the next 120 days = Yellow

Contract Expiration Date is more than 120 days out = Green

Contract Expiration Date is BLANK = Blue

I then need the parent to display in the following order based on what appears,

If any are red, display Red

If there are no red, but there are yellow, display Yellow

If there are no red or yellow but there are blue, display Blue

If there are no red or yellow or blue and all are green, display Green

This is currently what I have as the column formula but it won't work now that I have added in Parent/Child relationships

=IF([Contract Expiration Date]@row < TODAY(), "Red", IF(AND([Contract Expiration Date]@row >= TODAY(), [Contract Expiration Date]@row < TODAY(+120)), "Yellow", "Green"))


Best Answer


  • Emily McNeeley
    Emily McNeeley ✭✭✭✭✭

    Thank you @Cierr - This is very helpful and works well. I wish it worked as a column formula but appreciate your help with this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!