Track time an item was open (active)

Steve S

I have a date stamp column already in the SmartSheet. When a new row is added, a date and time are assigned. What would be the best method of tracking and reporting the time duration between when the row was added and when it was closed ?

I have ideas but have found it makes most sense to run it by one of you first. (Andree)

I welcome your guidance.

Paul Newcome

Once a row is closed, will there be other changes made to the row?

Andrée Starå

Hi Steve,

I also wonder like Paul if there will be any changes after.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Steve S

I am glad I asked because I would not have thought about the closure piece.

The answer is YES there is other data added to the row after my defined close.

1. When a new line is added via a user form, a date column marks day and time.

2. Once 11 users input their required data to the row (via Updated Request) we consider this row closed. This is where we would stop the clock on this row and tally that time duration (open to close) to my report. 

3. On a date out in the future (Date to submit order) the SmartSheet kicks of another Update Request to a sales rep. This rep inputs two pieces of data via a Update Request. This action does not change the open close duration time. 

Andree, I have shared my sheet with you in the past. I will share again now. 

Paul Newcome

You will need to manually establish the close time then. From there time calculations can get a little messy, but are possible with a handful of helper columns. Is your time 24 hour or 12 hour?

Steve S

24 hour clock

I do also have another column which counts the number of response entries so when all 11 managers have responded this count column turns to 11. Could I key off that somehow to mark a stop time ?

Andrée Starå

If you don't want to do it manually, you could use a third-party service like Zapier or similar to mark the stop time. Smartsheet API is another option.

Would that be an option?

Hope that helps!

Best,

Andrée

Steve S

I am willing to try it for sure as my Director requires our open and close duration. 

I will start researching the two items you listed. Smartsheet API and Zapier.

Which one do you recommend is least complicated and most reliable ?

 

Andrée Starå

I'd recommend trying Zapier first.

Best,

Andrée

Michele Thomas

Hello,

I have a similar concept that we are trying to work on. Our COO wants to track the amount of time it stage of sales takes.

So for example, the sales person initiates contact with the customer; to the first presentation; to getting the customer to sign a contract; and the start of construction. He wants a formula to track the amount of time it took to close and successful percentage rate. I'm at a total loss but saw this thread and it seemed similiar to what I want. 🤔

Paul Newcome

@Michelle Thomas

Are you able to provide screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed as well as provide more details as to your overall workflow and setup for this?

Does each milestone have it's own row or is it across multiple columns within the same row? How are you tracking he start and stop date/time? Are you using 12 or 24 hour time format?

Michele Thomas

@Paul Newcome

One of the issues I think is he dates on some and days on others to track the time; do you think using hours would be better? So he wants to see the amount of time between the "Qualified (Q) to Presentation Meeting (PM)". I am completely fine with changing/adding/deleting information to make this work. I'm just at a complete stand still and can't figure it out. 😣

Paul Newcome

@Michele Thomas My apologies. I had assumed you were tracking actual times. Do you only need to track number of days?

Michele Thomas

@Paul Newcome Yes, we only need to track days...which might make it easier?

Paul Newcome

MUCH easier! Do you want working days (Monday through Friday) or do you just want total days?

Total Days:

[End Date]@row - [Start Date]@row

Just replace the first column name with the end of the range and the second column name with the beginning. So if you wanted to track how many days were in between the date in the Q --> PM column and the date in the P --> Q column, you would have something like this (of course updated to actual column names):

=[Q --> PM]@row - [P --> Q]@row

If you wanted to know how many days total from the date in the P --> Q column all the way to the additional days listed in the PH-2 to PH-3 column, then you would subtract the date in the P --> Q column from the last date available (PM --> PR) then add the additional days.

=([PM --> PR]@row - [Q --> PM]@row) + SUM([PR --> PH-1]@row:[PH-2 to PH-3]@row)

Working Days:

For Working days, you would use the same concept except use a NETWORKDAYS function. So to use the examples above...

NETWORKDAYS([Start Date]@row, [End Date]@row)

NETWORKDAYS([P --> Q]@row, [Q --> PM]@row)

NETWORKDAYS([P --> Q]@row, [Q --> PM]@row) + SUM([PR --> PH-1]@row:[PH-2 to PH-3]@row)

Michele Thomas

@Paul Newcome I believe he wants total days but both of these help so much!! Thank you I cannot tell you how much I appreciate this!!