Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Time Tracking - Formula needed to total time spent

MelissaR1180
MelissaR1180 ✭
edited 12/09/19 in Archived 2017 Posts

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!

Tags:
· Share on FacebookShare on Twitter

Comments

  • Robert S.
    Robert S. Employee

    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. 

    · Share on FacebookShare on Twitter
  • MelissaR1180
    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 :(

    · Share on FacebookShare on Twitter
  • Robert S.
    Robert S. Employee

    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)

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories