Calculations on Times Formatted as Text

  • Thread starter Thread starter benneyb
  • Start date Start date
B

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.
 
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
 
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
 
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!
 
Back
Top