Military time clock to 100th hour

**bpatel** · Oct 12th, 2009, 12:27 PM

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

**Scott Huish** · Oct 12th, 2009, 01:27 PM

Hi, welcome to the board

I'm guessing that you are not looking for the answer of 20 minutes. What do those times represent?

**bpatel** · Oct 12th, 2009, 01:35 PM

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)

**Scott Huish** · Oct 12th, 2009, 02:03 PM

Is 13.11 equal to 1:11 PM, or is it 1 PM + 11/100ths hours?

**bpatel** · Oct 12th, 2009, 02:08 PM

1P.M. + 11/100 of an hour

**Xelas** · Oct 12th, 2009, 03:01 PM

Originally Posted by bpatel

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)

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.

**Scott Huish** · Oct 12th, 2009, 03:09 PM

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