Calculate days since a bubble turned red

Daryl Horrocks

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!

Mike Wilday

At this time there is no way to access the Cell history to get that date.  This has been a long-standing request of mine and would recommend submitting a prouduct enhancement request for this functionality. Being able to access a cell's history would allow us to mark the date a specific cell was modified. This would be great for marking the date a project was marked complete, the date a cell went red. etc. 

Daryl Horrocks

Thanks Mike. This would be a great enhancement; I'll submit the request.

J. Craig Williams

If you use Zapier and the number of Sheets that need this functionality is limited, this is a proven method for capturing a date when something happens:

http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/

It can be modified to use the RYG change to, I believe (but have not done so).

Craig

Daryl Horrocks

Thanks Craig, I'll check it out.