Hi, welcome to the board
I'm guessing that you are not looking for the answer of 20 minutes. What do those times represent?
I am trying to creata a worksheet to calculate hours worked in excel 2003. I have military time clock to 100th hour. How do I calcuate hours worked ? For example 14:30 is 14:50 in my time clock. Formating cell to military time does not work
Please help
Hi, welcome to the board
I'm guessing that you are not looking for the answer of 20 minutes. What do those times represent?
Office 2010/2016
It is used to calculate hours worked. For example clocked in time is 13.11 and clocked out time is 22.08
Also, there are times like in @ 18:18 and out @ 6:23( considered 3rd shift)
Is 13.11 equal to 1:11 PM, or is it 1 PM + 11/100ths hours?
Office 2010/2016
1P.M. + 11/100 of an hour
1st, it;s very important the the cells containing your military times are in text format. if you just type them per your example, Excel automatically formats them as times, and this throws everything off.
If A2="18:18", and B2="6:23", then:
C2=VALUE(LEFT(A2,(FIND(":",A2)-1)))
.. gives you the clock-in hours
D2=IF(VALUE(LEFT(B2,(FIND(":",B2)-1))).. gives you clock-out hours. The IF statement adds 24 hours to this time if the hour is less than the clock-in hours.
E2=VALUE(RIGHT(A2,2))
.. gives you clock-in 1/100's of an hour
F2=VALUE(RIGHT(B2,2))
.. gives you clock-out 1/100's of an hour
G2=D2-C2+(F2-E2)/100
.. ties it all together, and gives you the hours worked. In this case, the answer would be 12.05
If you want one big formula, then just substitute the formula (for example, "VALUE(LEFT(A2,(FIND(":",A2)-1)))" for the address (in this case "C2") in the formula for G2.
If you had a consistent format, this would be much simpler. For example, if you had all of your values with decimal points, this could be fixed quite easily with the DOLLARDE function (prior to XL2007, you may need to load the Analysis Toolpak for this):
=MOD(DOLLARDE(B1/24,100)-DOLLARDE(A1/24,100),1)*24
Sheet1
A B C D 1 13.11 22.08 8.97
Spreadsheet Formulas
Cell Formula D1 =MOD(DOLLARDE(B1/24,100)-DOLLARDE(A1/24,100),1)*24
Excel tables to the web >> Excel Jeanie HTML 4
Last edited by Scott Huish; Oct 12th, 2009 at 03:11 PM.
Office 2010/2016
Like this thread? Share it with others