Results 1 to 7 of 7

Military time clock to 100th hour

This is a discussion on Military time clock to 100th hour within the Excel Questions forums, part of the Question Forums category; I am trying to creata a worksheet to calculate hours worked in excel 2003. I have military time clock to ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Post 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


    Please help

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,003

    Default 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?
    Office 2007/2010

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Default 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. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,003

    Default Re: Military time clock to 100th hour

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

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Default Re: Military time clock to 100th hour

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

  6. #6
    New Member
    Join Date
    Jul 2008
    Posts
    5

    Default Re: Military time clock to 100th hour

    Quote Originally Posted by bpatel View Post
    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. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,003

    Default 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

     ABCD
    113.1122.08 8.97

    Spreadsheet Formulas
    CellFormula
    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 2007/2010

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com