Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
Calculations using Times in Excel 2003 | Excel Worksheet Functions | |||
times and Calculations | New Users to Excel | |||
Difference in dates calculations except between certain times. | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |