Tracking the Specific Time of a Cell in One Status

Adrià Termes

Hi all.

I am trying to simply and easily track the time in days a cell is in red status (General Health column in picture below) until it changes. Nonetheless, although we can easily keep track of row changes with helper columns, I really cannot figure out how to do so specifically for one cell.

Any recommendation on how to carry on that specific calculation? I have thought of support columns and many other solutions, but they either don't update automatically when something becomes or it is no longer red. Does anybody have a clear picture on how to tackle it? It would be highly appreciated.

Thank you!

Andrée Starå

https://community.smartsheet.com/discussion/109570/tracking-the-specific-time-of-a-cell-in-one-status

Hi,

I hope you're well and safe!

You can add Workflows combined with the Record a date action and then formulas to measure the days.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nick Korna

HI @Adrià Termes

Set up 2 helper date columns and some automation. The first automation captures the date General Health becomes Red and the second one when it changes to another value (E.g. Green and Yellow for a 3 status column).

A column formula in the "Amount of days in RED" column can then check the difference between the 2 dates (combining with an IF statement to check there is a date in the "start" column if necessary). For example, if your helper columns were named "Red start" and "Red end":

=IF([Red start]@row <> "", [Red end]@row - [Red start]@row, "")

Hope this helps, but if you've any questions on anything then just ask! ☺️

Adrià Termes

Hi @Nick Korna , @Andrée Starå .

Tried both solutions for my learning sake: both of them work like butter! Thank you very much indeed for your help and for clarifying my mind!

Andrée Starå

https://community.smartsheet.com/discussion/comment/393122#Comment_393122

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Krystal Garcia

Anyway to do this but including a time stamp? I have a similar use case but need to track by the minute/hour instead of by day @Andrée Starå

Andrée Starå

Hi @Krystal Garcia

I hope you're well and safe!

Yes, you can use my method to track the minute/hour.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Krystal Garcia

@Andrée Starå Can you provide the formula to capture the time because the one above is just for days

Andrée Starå

@Krystal Garcia

To clarify. You can't do it with a formula, but you can use my method described below.

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!