Calculations on Times Formatted as Text

[benneyb]

I would like to calculate the expense associated with the time usage of
something, given a rate per hour, and times formatted as Text such as below:

1535 1700

1535 represents 3:35 pm, and 1700 represents 5:00 pm. I can calculate the
difference between the two, and get 3:25 for example, but not sure how to
express/format this to get the desired 3 & 25/60 hours times "x" rate.

Thanks in advance.

 

[benneyb]

Apologies for the bad math - difference is of course 1:25, not 3:25

 

[Jon Peltier]

You could keep these as actual times (H:MM) but use a number format like
HHMM to give you the military time appearance. Do the subtraction, which is
stored internally as H:MM and displayed using the same HHMM number format.
Convert this result to whole number hours plus fraction of minutes by
dividing by one hour, or multiplying by 24. Then multiply by the hourly
rate.

- Jon

 

[Gary''s Student]

try:

=TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0)

 

[T. Valko]

I can calculate the difference between the two, and get 3:25

What formula do you use to get that result?

You might be able to wrap that inside the TIMEVALUE function:

=TIMEVALUE(your_formula)*24*rate

 

[benneyb]

Thanks All,

I got it with a combinaton of Gary's Formula and Jon's calculation of
multiplying by 24 - this gave me the number of hours. Many thanks for the
very prompt replies!