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.

https://community.smartsheet.com/discussion/84838/how-to-calculate-time-in-smartsheet

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.

Paul Newcome

@Coop22 Calculating duration across dates with time included is a solution already included on one of the pages in this thread:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Coop22

@Paul Newcome is it the Date/Time/Year one?

Paul Newcome

Calculating Time Worked For Employees seems more applicable to your use case.

Coop22

@Paul Newcome ahhhh yes, that was specifically the one I was referencing in my original post, that you had a solution, but I didn't want to have to change the time to 24 hour!

Paul Newcome

There are multiple posts in the thread as well that show how to convert 12 hour to 24 hour to continue with the other formulas since 24 hour is easier to manipulate. There are also a number of posts in the thread that outline starting from a 12 hour time and converting directly into an integer for calculations.

There is also now a TIME function that could simplify a lot of this process although I don't think it can account for dates. I do think that the overall formulas could be simplified using the TIME function though. I just haven't fiddled with it much yet.

Coop22

@Paul Newcome I see the TIME function now, but it seems like it may not be completely done yet. One of the Usage Notes states that you would reference a cell in a Date/Time Column, but I don't see where we have a Date/Time column available. Unless they're referring to Modified/Created columns...

Paul Newcome

You can also reference a text/number column that has a time entered.