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å

Tracking the Specific Time of a Cell in One Status

Adrià Termes

Aug 29, 2023

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.

https://us.v-cdn.net/6031209/uploads/FAENJ84O3F0K/image.png

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!

---

View Post

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å

Adrià Termes

Aug 30, 2023

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!

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: 

Lock or Store Date/Value Solution without using Zapier

Andrée Starå

May 24, 2020

Hi all,

I hope you're well and safe!

I've developed a solution that lets you store the date or value.

You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.

We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/value that we can use, it will work.

Also, this opens up more options with auto-numbering and similar.

Depending on other workflows, you could reuse one sheet for multiple workflows.

I hope that helps!

Let me know if you have any questions!

Stay safe and have a fantastic day!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as helpful. It will make it easier for others to find a solution.

---

View Post

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!