Calculations on Times Formatted as Text
 

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.

Calculations on Times Formatted as Text
 

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

"benneyb" wrote:

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 1:25 for example, but not sure how to
express/format this to get the desired 1 & 25/60 hours times "x" rate.

Thanks in advance.

Calculations on Times Formatted as Text
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"benneyb" wrote in message
...
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.

Calculations on Times Formatted as Text
 

try:

=TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0)
--
Gary''s Student - gsnu200802


"benneyb" wrote:

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

"benneyb" wrote:

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 1:25 for example, but not sure how to
express/format this to get the desired 1 & 25/60 hours times "x" rate.

Thanks in advance.

Calculations on Times Formatted as Text
 

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

--
Biff
Microsoft Excel MVP


"benneyb" wrote in message
...
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.

Calculations on Times Formatted as Text
 

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!

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"benneyb" wrote in message
...
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.