4

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
5

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.

1
  • 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

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.