hh:mm:ss format??

miribilist

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

J. Craig Williams

Miribilist,

 

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

 

If your data is padded (that is 03:09;07 instead of 3:9:7)

 

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