Announcement

Collapse
No announcement yet.

MILITARY time conversion

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MILITARY time conversion

    I need to enter a clock in and clock out time and then convert this to hours.

    I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

    But I keep getting the #NAME Error....

    Can someone please help me out?

  • #2
    Re: MILITARY time conversion

    Originally posted by Unregistered
    I need to enter a clock in and clock out time and then convert this to hours.

    I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

    But I keep getting the #NAME Error....

    Can someone please help me out?
    please convert 20:25 and 16:50

    Comment


    • #3
      Re: MILITARY time conversion

      all I can tell u is 20:25 = 8:25 pm and 16:50 = 4:50 pm..the military clock is simply a 24 hour clock with 24 hours vice having two sets of 12 hour time frames. 1pm is 1300 hours, 1 am is 0100 hours.

      Comment


      • #4
        Re: MILITARY time conversion

        General hints:

        Break the formula up into pieces and see if the pieces work,

        QUOTIENT(B1,100) in one cell and
        QUOTIENT(A1,100) in another cell

        if that works, put them together and try
        (QUOTIENT(B1,100)-QUOTIENT(A1,100))/24

        Continue on with other parts of the formula until you discover what is causing the error.

        RB

        Comment


        • #5
          Re: MILITARY time conversion

          I am having problems converting time to hours. I am looking for a way to convert it or to understand it in a more simple way. What I do when the
          employee clock in I take the time against the audit report which has hours on it and convert the clock in time to hours.



          Help,

          Comment


          • #6
            Re: MILITARY time conversion

            Originally posted by Unregistered
            I need to enter a clock in and clock out time and then convert this to hours.

            I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

            But I keep getting the #NAME Error....

            Can someone please help me out?
            QUOTIENT

            Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
            If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.


            On the Tools menu, click Add-Ins.
            1. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
            2. If necessary, follow the instructions in the setup program.

            Comment


            • #7
              Re: MILITARY time conversion

              in excel format your cell to military time you can put in the regular time ie 3:30 PM and it will convert it to military time

              Comment


              • #8
                Re: MILITARY time conversion

                Originally posted by Unregistered
                I need to enter a clock in and clock out time and then convert this to hours.

                I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

                But I keep getting the #NAME Error....

                Can someone please help me out?
                The information from the Cheez in Post #6 enables the use of the QUOTIENT function and is one way to solve the problem.

                If you cannot or do not want to load the Analysis ToolPak, there is an alternative solution. The following formula produces the same results as the QUOTIENT formula, but uses Excel intrinsic functions only:
                =(TRUNC(B1/100)-TRUNC(A1/100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)
                Don't forget to format the output cell(s) using a 24-hour time format, such as "13:30".

                Comment

                Working...
                X