Hours:Minutes:Seconds reported by consultant at $/hour calculated to the hr/min/sec used

Options

I have a consultant who bills at the hourly rate down to the Hour:Min:Seconds time used.

They provide us with Time in this format 1:13:51. How do I take that time and multiply it by the rate to give correct cost per time in USD?

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 04/29/24 Answer ✓
    Options

    Depending on where you are pulling the number or hours and hourly rate from, this will need to be adjusted somewhat to fit your use case.

    =Rate@row * (IF(LEN(Hours@row) > 5,(VALUE(Left(Hours@row, LEN(Hours@row) - 6))), 0) + (VALUE(MID(Hours@row, LEN(Hours@row) - 4, 2)) / 60) + (VALUE(RIGHT(Hours@row, 2)) / 3600))

    Edit: Adding screenshot to show how I set this up.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 04/29/24 Answer ✓
    Options

    Depending on where you are pulling the number or hours and hourly rate from, this will need to be adjusted somewhat to fit your use case.

    =Rate@row * (IF(LEN(Hours@row) > 5,(VALUE(Left(Hours@row, LEN(Hours@row) - 6))), 0) + (VALUE(MID(Hours@row, LEN(Hours@row) - 4, 2)) / 60) + (VALUE(RIGHT(Hours@row, 2)) / 3600))

    Edit: Adding screenshot to show how I set this up.

  • Elayne Smith
    Options

    PERFECT! Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!