Calculate days since a bubble turned red

I have a column that shows red (off track), yellow (at risk), or green (on track) depending on whether a date column (completion) is before or after another date column (goal completion date). What I'd like to show is the amount of days since a bubble turned red. This would tell me how many days it has been since a project went to off track status.

Is this possible? I see in the cell history that it has the date a cell turned from green to yellow or red, but how do I surface that date in order to calculate against it?

Thanks!

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!