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