#### 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.

# hh:mm:ss format??

edited 12/09/19

I am logging time spent per job using timesheet software, and the data it exports is in hh:mm:ss format.  Is this date useable in Smartsheet?

From this, I want to calculate a job cost.

In Excel, I would do this by using the following formula:

= hh:mm:ss  x  hourly rate  x  hours in a day (24)

This would give me a £ value for the number of hours/minutes.

Smartsheet doesnt seem to recognise this.

Any help appreciated....

• ✭✭✭✭✭✭

Miribilist,

The Date type column is date-only, so you'll want to import it as text.

This formula will tell you the hours

=VALUE(LEFT(HHMMSS2, 2)) + VALUE(RIGHT(LEFT(HHMMSS2, 5), 2)) / 60 + VALUE(RIGHT(HHMMSS2, 2)) / 3600

this will tell you the minutes

=VALUE(LEFT(HHMMSS3, 2)) * 60 + VALUE(RIGHT(LEFT(HHMMSS3, 5), 2)) + VALUE(RIGHT(HHMMSS3, 2)) / 60

and this will tell you the seconds

=VALUE(LEFT(HHMMSS3, 2)) * 3600 + VALUE(RIGHT(LEFT(HHMMSS3, 5), 2))*60 + VALUE(RIGHT(HHMMSS3, 2))

Add ROUND() to round off the total if necessary.

If your data is NOT padded, it gets a little more complicated (and I would add hidden columns to remove the complexity from the end-user's view)

If you data is a time stamp and not just a total time, then that too can be taken care of fairly easily.

I hope this helps.

Craig

This discussion has been closed.