Tracking Time

Coop22 ✭✭✭✭

Hopefully this post will help someone looking for a solution for dealing with time in Smartsheet outside of Resource Management. (Bolded the summary for quick access)

I've recently run into an issue with a project. We are maintaining a lot of our data in Smartsheet, but a recent project got brought up about wanting to track the time spent on projects. This would then be used to bill clients and to show other interested parties how much time a team member was spending on a group and on what. Smartsheet obviously doesn't recognize time values and the community didn't have a solution that I particularly loved, so we needed to think of a work-around.

@Paul Newcome had a couple of solutions that would work, but didn't quite satisfy what I was needing. So maybe this can be added to the consolidated post below if it works out.

I started thinking about the fact that Smartsheet can't recognize time, but can recognize values. Hence, we decided to convert time to a value.

To do so, we created 3 columns for the clock in time (would still love for this to be a button click instead). The 3 columns are "Clock in Hour", "Clock in Minutes", and "Clock in Period". Essentially, instead of typing 3:00 PM in a cell, you type 3, then tab and type 00, then tab and type PM. We ran into an issue with the 00 being converted to text in the minutes column, so we added a hidden "Minute_Check" column. Below is the column formula in the "Minute_Check" column:

=IF([Clock in Minutes]@row = "00", 0, [Clock in Minutes]@row)

The below formula then converts those three values into a "Time Value", which is recorded in another hidden column called "Clock in Value".

=IF([Clock in Period]@row = "AM", IF([Clock in Hour]@row = 12, [Minute_Check]@row, ([Clock in Hour]@row * 60) + [Minute_Check]@row), IF([Clock in Hour]@row = 12, [Minute_Check]@row + 720, (([Clock in Hour]@row * 60) + [Minute_Check]@row) + 720))

I know this can be condensed a bit (including embedding the "Minute_Check" formula into the "Clock in Value" formula if you wanted) and some of the parentheses can be removed, but it makes it easier for me to follow in these longer formulas.

This formula allows you to put in the time and allows it to differentiate between AM and PM.

Then to calculate the duration of a clock, we replicate these columns and formulas for the clock out times and simply subtract the clock out value from the clock in value.

For the purposes of our project, we need to calculate the hours spent on the project and multiply that value by the hourly price of the service. To do so, we subtract the clock out value from the clock in value and divide by 60, then multiply by the hourly rate.

The next step in what we're doing with it is going to be adding in a clock in date and calculating a date value similar to how Excel handles dates, except we're going to use 1/1/2000 instead of 1/1/1900, since we won't have a need for tracking any dates/times in the past in this particular project. This should allow us to calculate the duration of a clock in/out that goes into the next day, since the above wouldn't work in this situation.