Help with calculated columns / data manipulation to display in dashboards

Rachel Dean

It's quite a long one, so bear with me!

We have a process where Program Managers provide a status update (among other things) on their programs every 2 weeks (On Track, At Risk, Severe Risk etc.) A program could be sitting at "At Risk" for a 12 months, and this is what I want to display on my dashboard. I want to know what the previous status was as this can show me whether we are trending the right way to On Track and how long it has been "At Risk".

For background, each program has an update sheet which program managers complete every 2 weeks, once submitted this data updates the program dashboard and auto copies the data onto an archive sheet, new rows are added at the bottom of this archive sheet. So I have all the data stored I just need help manipulating and displaying it. Some program managers provide an update more or less frequently, so 2 weeks isn't a hard and fast rule.

Below is what I have at the moment, this report gives me the status from all the updates which is great but it's too much scrolling & data, I want a clean and simple view. In this example you can see the status changed from On Track to At Risk on 24th March. So what I want to display is the Current Status = "At Risk", Status Changed = 24/03/23 or 110 days ago, Previous Status = "On Track", in a nice neat grid.

I'm thinking the best way is to have some calculated columns in my archive sheet to identify the last status change row and then reference the date of that to work out the time difference. But no idea where to start with the calculation to identify the last status change row.

Open to any idea anyone has... Thank you! 

Mike Wilday

Based on a quick review of your question, it sounds like you might benefit from the "record a date" automation which you could use to capture the date the update was submitted... then you could do something with the max or min-date and the status? Does that make sense?

Rachel Dean

https://community.smartsheet.com/discussion/comment/384813#Comment_384813

Thanks Mike. I use the Created date column which auto date stamps the updates, these are shown in the screenshot. How can I use the MIN / MAX formulas to scan through the archive data and identify the row when the status changed. Do you know what that formula would look like?

Mike Wilday

Out of hand, by finding the earliest date that the status equaled At Risk... then you could compare that date against the max (lastest) status date to reveal how long the status has been that way.

Drew Barker

How about an automation = "Record a Date" when the Status field changes to "Any Value"? Then every row is a change to the status without all of the dupes in between.