Military time clock to 100th hour

bpatel

New Member
Joined
Oct 12, 2009
Messages
3
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi, welcome to the board

I'm guessing that you are not looking for the answer of 20 minutes. What do those times represent?
 
Upvote 0
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)
 
Upvote 0
Is 13.11 equal to 1:11 PM, or is it 1 PM + 11/100ths hours?
 
Upvote 0
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)))<C2,VALUE(LEFT(B2,(FIND(":",B2)-1)))+24)
.. 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.
 
Upvote 0
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

Excel Workbook
ABCD
113.1122.088.97
Sheet1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top