Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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?
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.
*24
mid(A2,5,2) to accommodate for the : character
=(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.
