IF Function Due Date

I am attempting to create a "Current Status" column that is using the Harvey Balls (red, yellow, green and gray). I am wanting these to automatically change yellow when my "End Date" column is 90 days prior to todays date. If the "End Date" is 60 days before todays date, then I want it to change from yellow to green. If the "End Date" column is greater than todays date, I would like it be red and stay red until the until a date is entered in the "Submitted Date" column then change to gray. 

=IF(ISDATE([Submitted Date]@row), "Gray", IF(TODAY() >= ([End Date]@row), 90, "Yellow", IF(TODAY() >= ([End Date]@row), 60, "Green", "Red"))))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!