Military time clock to 100th hour

Thanks:  0
Likes:  0

# Thread: Military time clock to 100th hour

1. ## Military time clock to 100th hour

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

2. ## Re: Military time clock to 100th hour

Hi, welcome to the board

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

3. ## Re: Military time clock to 100th hour

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)

4. ## Re: Military time clock to 100th hour

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

5. ## Re: Military time clock to 100th hour

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

6. ## Re: Military time clock to 100th hour

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.

7. ## Re: Military time clock to 100th hour

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

 Cell Formula D1 =MOD(DOLLARDE(B1/24,100)-DOLLARDE(A1/24,100),1)*24

Excel tables to the web >> Excel Jeanie HTML 4

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•