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
Β
