Multiply duration (in hours) by decimal value

+----------+------+--------+
| Duration | Rate |  Value |
+----------+------+--------+
| 16:15:00 | 55   | 893.75 |
+----------+------+--------+

I would like to use a formula to calculate the value cell. I have tried the following but I think HOUR only works for TIME values not DURATION format.

=((HOUR(B1) + MINUTE(B1)/60 + SECOND(B1)/3600)*B2)

What is the best way to automatically calculate the Value cell?

If your Duration is a string then something like:

=(left(A2,2)+mid(A2,4,2)/60+right(A2,2)/3600)*B2

otherwise:

=A2*B2*24

with the result formatted as Number.

• times 24 was messssing me up. =HOUR(duration) doesnt do shiz. and formatting automatically turns out some wierd stuff. formatting as number or currency works it out. but dang i would have never figured out that *24 Feb 4 '20 at 9:26
• was doing this on google sheets and had a very similar situation, however I had to do mid(A2,5,2) to accommodate for the : character Dec 2 '21 at 13:30

=(left(A2,2)+mid(A2,4,2)/60+right(A2,2)/3600)*B2

This formula doesn't work systematically as the first part can be 1 or 2 digit numbers.

Ex : 7:30:00 and 11:00:00

For single digit hours, everything will be shifted and the formula will includes some ":" .

To be sure this formula works, you have to avoid format "duration" which can displays something like 7:30:00. "Automatic" format for the time will display 07:30 in my situation and the formula will work.

The accepted answer only works if there is exactly two digits in the columns and even then it does not accommodate for the : characters. After some work I found that the following works better.

=(index(split(A2,":"),0,1)+(index(split(A2,":"),0,2)/60)+(index(split(A2,":"),0,3)/3600))*A3

The way this works is using the split operator to use the : character as a delineator then grabbing the indexed column of the returned values. That can then be used to multiply by the rate.