5

I have the following data in a Google Spreadsheet:

+----------+------+--------+
| 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?

0
6

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.

2
  • 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
    – austin
    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
    – zmanc
    Dec 2 '21 at 13:30
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.