Time Tracking - Formula needed to total time spent

MelissaR1180

Has anyone used SS to track actual time spent? I am using the "Time Track for Client Projects" template modified for simple tracking of time spent for individual tasks by client or department. I cant seem to get the formula needed to add the columns for Start time & Stop Time to calculate and provide a Duration for actual time spent on the specific task.

I've used the SUM and entered the formula for Stop Time - Start time to get the total for the column that = Duration. It works fine if the start and end times are logical (4.5 = 4:30 and 5.25 = 5:15) but how can we calculate if the times are a bit trickier and the start time is 4:10 and stop time is 5:05?

Thanks!

Robert S.

Hello,

 

Thanks for the question. Currently Smartsheet doesn't have any built in time functions. This is something that I know if being worked on, however I don't have any expected release for this. If you'd like to add this as a feature to future updates, I recommend submitting a Product Enhancement Request by using the link to the right in the "Quick Links" section.

 

As a possible workaround for this, you could have a formula that converts a time entered as HH:MM to a decimal value and within the same formula find the difference between the start time and stop time. This gets tricky however if you're using AM and PM vs 24 hour time. Here's an example of how this could be written with 24 hour time and a consistent format of HH:MM:

 

=(((VALUE(LEFT([Stop time]3, FIND(":", [Stop time]3) - 1)) * 60) + VALUE(RIGHT([Stop time]3, FIND(":", [Stop time]3) - 1))) / 60) - (((VALUE(LEFT([Start time]3, FIND(":", [Start time]3) - 1)) * 60) + VALUE(RIGHT([Start time]3, FIND(":", [Start time]3) - 1))) / 60)

 

Keep in mind that with this formula, if the start time or stop time are written as H:MM or not in 24 hour time (e.g. 1:30PM should be 13:30, and 1:30AM should be 01:30), the issue may not work as expected and could even give an error. 

MelissaR1180

Thank you for the formula. I did try this:

=(((VALUE(LEFT([Stop Time]3, FIND(".", [Stop Time]3) - 1)) * 60) + VALUE(RIGHT([Stop Time]3, FIND(".", [Stop Time]3) - 1))) / 60) - (((VALUE(LEFT([Start Time]3, FIND(".", [Start Time]3) - 1)) * 60) + VALUE(RIGHT([Start Time]3, FIND(".", [Start Time]3) - 1))) / 60))

And changed the entries to HH:MM (08:30) but get the error UNPARSEABLE. I may just have to go back to excel to track this for now

Robert S.

Hello,

 

This error is happening as there's an extra parenthesis at the very end. If that's removed, you may also get an #INVALID VALUE error. This would happen as your formula is looking for a period "." rather than a colon ":".

 

If your start and stop times are formatted as HH:MM then you'll need to change the "." to ":" like this:

 

=(((VALUE(LEFT([Stop Time]3, FIND(":", [Stop Time]3) - 1)) * 60) + VALUE(RIGHT([Stop Time]3, FIND(":", [Stop Time]3) - 1))) / 60) - (((VALUE(LEFT([Start Time]3, FIND(":", [Start Time]3) - 1)) * 60) + VALUE(RIGHT([Start Time]3, FIND(":", [Start Time]3) - 1))) / 60)

 

If your start and stop times are formatted as HH.MM then you can leave the "." and just take away the last parenthesis like this:

 

=(((VALUE(LEFT([Stop Time]3, FIND(".", [Stop Time]3) - 1)) * 60) + VALUE(RIGHT([Stop Time]3, FIND(".", [Stop Time]3) - 1))) / 60) - (((VALUE(LEFT([Start Time]3, FIND(".", [Start Time]3) - 1)) * 60) + VALUE(RIGHT([Start Time]3, FIND(".", [Start Time]3) - 1))) / 60)