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?

Improve this question
0

4 Answers 4

Reset to default
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.

Improve this answer
1
  • Even if it is a string multiplying it by a number will coerce it to a duration.
    – Blindspots ♦
    Commented Apr 22 at 19:57
2

Regardless whether your values are formated as datetime, dates, times, or durations, Google Sheets stores the underlying value as a fraction of days.

For example, the three hour duration 3:00:00 is stored as 0.125 because 3 hrs = 3/24 days

In order to convert your time to fractions of hours instead of fractions of days, you can multiply your duration by 24.

For example:

=duration * 24 * hourly rate
=A2 * 24 * B2
="16:15:00" * 24 * 55
=16.25 * 55
=$893.75
Improve this answer
3
  • I edited your answer to make it more clear and correct rather than writing my own. If you have any concerns feel free to make changes or roll it back as you prefer. If you acknowledge this comment I will remove it.
    – Blindspots ♦
    Commented Apr 22 at 19:56
  • Eric's answer should be the accepted one. It will work for both durations as strings as well as duration formatted numbers.
    – Blindspots ♦
    Commented Apr 22 at 19:58
  • Welcome to Web Applications. Consider registering your account to avoid accidentally losing access to your content by switching devices, browsers, or clearing your cookies. Registering is quick and you gain access to more site features. For more info see Why should I register my account?
    – Blindspots ♦
    Commented Apr 23 at 0:18
-1

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

Improve this answer
-1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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