hh:mm:ss Time Tracking in Smartsheets? (Probably not possible)

James@BW · 10/23/18

Howdy everyone!

I've got a pernicious little problem that I'm wondering if anyone has any ideas about.

My team and I currently track all of our break/fix help tickets in one dedicated "backlog" sheet. Each task that we need to complete is added to this sheet, gets prioritized, gets worked on, gets clarified (if needed), and is eventually completed. After a task is completed, we discuss the outcome as a team and then move the task to another sheet which holds all of our completed work for future reference.

As mentioned above, there are a number of statuses that each task moves through before being eventually 'complete'. These statuses are tracked via a column called "Build Status" which features the values of 'Not started', 'Working', 'Roadblocked', 'Submitted', and 'Completed'.

I would like to begin tracking the *amount of time* (ideally in hours:minutes:seconds format, but I'll take what I can get) that a given tasks spends in each of these stages.

Example Scenario:

For instance, let's say that I have a task called "make coffee". Originally, the task would be at 'Not Started' while it waits on the backlog to get processed by the team. Once a rep starts the "make coffee" task, the rep changes the status to 'Working'. While making the coffee, the rep realizes they don't know what type of coffee the person would like (is it Iced Coffee? Hot Coffee? French Press? Aeropress? etc....). While the rep asks the requestor what type of coffee they'd like, the Build Status of the "make coffee" task is set to 'Roadblocked'. The rep takes a 15 minute break while they wait for the response from the requestor. After the break, the rep gets confirmation from the requestor that they want iced coffee with 2 creams and 2 sugars. Now that the task is ready for more work, the rep changes the Build Status back to 'Working'. After the coffee is made, the rep brings the coffee by the requestor's office (who isn't there), so the rep leaves the coffee for the requestor and moves the Build Status to 'Submitted'. Once the requestor gets back to their office, they send an email to the rep thanking them for making the coffee and responds that the coffee is wonderful. The rep then changes the Build Status to 'Completed'.

Here is a list of the status changes (in order) which happened in this example:

-- Task created and originally set to 'Not Started'

-- Changed from 'Not Started' to 'Working'

-- Changed from 'Working' to 'Roadblocked'

-- Changed from 'Roadblocked' to 'Working'

-- Changed from 'Working' to 'Submitted'

-- Changed from 'Submitted' to 'Completed'

What I'm looking for:

I want to know if Smartsheets has a way for me to measure the hours:minutes:seconds time that this task spent at each of the Build Status values. For instance, if this task was requested at 8:00am and didn't finish until 10:30am, 2hours and 30minutes worth of time should be tracked in the various statuses of the Build Status column.

Ideally, this would be done AUTOMATICALLY in the background as the task is worked. Essentially, what would be perfect is if Smartsheets could begin a counting the time spent in a given status (in seconds) as soon as a task is set to a given Build Status value. I imagine this would take some sort of formula, and I'm really hoping that it doesn't require some external add-on that we'd need to attach to Smartsheets.

If we were able to capture this data, it would then allow us to report on how long a task was in each of the Build Status stages. This is valuable to us because we want to be able measure how long we've spent working a task vs. how long we've been roadblocked on a task in order to better inform leadership of our workflow and bottlenecks.

Is this possible on this platform?

Andrée Starå · 10/23/18

Hi James,

Unfortunately, I don't think this is possible with Smartsheet, but it could be possible with an add-on.

Is it still relevant if it's possible with an add-on?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome · 10/23/18

It is possible in SS as long as you don't mind manual entry of the times and rather convoluted work-arounds and formulas for the calculations...

Andrée Starå · 10/23/18

I can see many, many (many) columns for this to work

Paul Newcome · 10/23/18

Using Created Date and Modified Date columns plus the column for the difference and a 12 hour clock, I end up with 12 helper columns.

Using manual entry for dates and times with a 24 hour clock is 6 columns down to the minute. Seconds would take 8 columns. That's having it displayed as "## days ## hours ## minutes ## seconds"

To display as " ##:##:##:## ", that would cut the column count down to 4.

Again... This is all manual entry (of sorts) for date and time since SS doesn't support time stamp of a specific action other than created and modified.

I have found that converting all of them into the smallest necessary denomination, adding them together, and doing your calculations, then breaking it back apart is the easiest way to write everything. In this case it would be seconds. 86,400 seconds in a day. 3,600 in an hour. 60 in a minute.

Andrée Starå · 10/23/18

That won't be enough because you have to account for what change happened when. I've set up a similar solution with who performed a task and start/end time and multiple entries and almost maxed out the columns.

Paul Newcome · 10/23/18

@Andree

My calculations were for a single start/finish time per row as that's how I have my tasks broken down. For multiple entries per row... Yeah. It increases significantly.

Below is a breakdown and screenshot of single task/24 hour clock/days included/broken down to the minute on a testing sheet. Columns are as follows:

Location: Manual Entry

Start Date: Manual Entry

Finish Date: Manual Entry

Start Time: Manual Entry

Finish Time: Manual Entry

Total: =SUBSTITUTE(JOIN(Date1:[Minute Help]1, " "), "-", "")

Date: =IF(IF(VALUE([Finish Time]1) < VALUE([Start Time]1), ([End Date]1 - [Start Date]1) - 1, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), [End Date]1 - [Start Date]1)) = 0, "-", IF(VALUE([Finish Time]1) < VALUE([Start Time]1), ([End Date]1 - [Start Date]1) - 1, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), [End Date]1 - [Start Date]1)))

Date Help: =IF(Date1 = "-", "-", IF(Date1 = 1, "Day", IF(Date1 > 1, "Days")))

Hour: =IF(IF(AND(VALUE(RIGHT([Finish Time]@row, 2)) < VALUE(RIGHT([Start Time]@row, 2)), VALUE(LEFT([Finish Time]@row, 2)) < VALUE(LEFT([Start Time]@row, 2))), 23 - (VALUE(LEFT([Start Time]@row, 2)) - VALUE(LEFT([Finish Time]@row, 2))), IF(VALUE(LEFT([Finish Time]@row, 2)) < VALUE(LEFT([Start Time]@row, 2)), 24 - (VALUE(LEFT([Start Time]@row, 2)) - VALUE(LEFT([Finish Time]@row, 2))), IF(AND(VALUE(LEFT([Finish Time]@row, 2)) = VALUE(LEFT([Start Time]@row, 2)), VALUE(RIGHT([Finish Time]@row, 2)) < VALUE(RIGHT([Start Time]@row, 2))), 23, IF(VALUE([Finish Time]@row) >= VALUE([Start Time]@row), VALUE(LEFT([Finish Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2)))))) = 0, "-", IF(AND(VALUE(LEFT([Finish Time]1, 2)) - VALUE(LEFT([Start Time]1, 2)) = 1, VALUE(RIGHT([Finish Time]1, 2)) < VALUE(RIGHT([Start Time]1, 2))), "-", IF(AND(VALUE(RIGHT([Finish Time]@row, 2)) < VALUE(RIGHT([Start Time]@row, 2)), VALUE(LEFT([Finish Time]@row, 2)) < VALUE(LEFT([Start Time]@row, 2))), 23 - (VALUE(LEFT([Start Time]@row, 2)) - VALUE(LEFT([Finish Time]@row, 2))), IF(VALUE(LEFT([Finish Time]@row, 2)) < VALUE(LEFT([Start Time]@row, 2)), 24 - (VALUE(LEFT([Start Time]@row, 2)) - VALUE(LEFT([Finish Time]@row, 2))), IF(AND(VALUE(LEFT([Finish Time]@row, 2)) = VALUE(LEFT([Start Time]@row, 2)), VALUE(RIGHT([Finish Time]@row, 2)) < VALUE(RIGHT([Start Time]@row, 2))), 23, IF(VALUE([Finish Time]@row) >= VALUE([Start Time]@row), VALUE(LEFT([Finish Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2))))))))

Hour Help: =IF(Hour1 = "-", "-", IF(Hour1 = 1, "Hour", IF(Hour1 > 1, "Hours")))

Minute: =IF(ABS(VALUE(RIGHT([Finish Time]1, 2)) - VALUE(RIGHT([Start Time]1, 2))) = 0, "-", ABS(VALUE(RIGHT([Finish Time]1, 2)) - VALUE(RIGHT([Start Time]1, 2))))

Minute Help: =IF(Minute1 = "-", "-", IF(Minute1 = 1, "Minute", IF(Minute1 > 1, "Minutes")))

The formula in the Hour column is so long because that's where I factored in all of the variables such as even if the date is tomorrow, if the finish time is before the start time, it still isn't 1 day. And all that other mess with minutes and whatnot.

See the below screenshot to see it working and feel free to double check any of the calculations and whatnot.

DISCLAIMER:

This was still in my earlyish days in SS, so some of it may be simplified further. It was also before my discovery of JOIN(COLLECT. Time can be such a pain to calculate in SS, that I have avoided changing it since it is working as is.

L_123 · 10/23/18

https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

My solution is above. I haven't updated it in several months, but I don't think any formulas have been added to streamline it further.

(its actually 2 solutions separated by the black line, one for updating via modified and one for updating with date time columns

Paul Newcome · 10/23/18

@Luke

I remember seeing your's a while back. I can't remember why I built my own instead of just hijacking your's.

Formulas and Functions

Formulas and Functions

hh:mm:ss Time Tracking in Smartsheets? (Probably not possible)

Comments

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Is there a way to make a field in a form auto populate street address as its being entered?

IF formula for INDEX/MATCH across sheets

NETDAYS Formula Question