Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Calculate Military Times Without Colon

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate Military Times Without Colon

    I just want to shear a formula which will help if you to calculate time stored as text for example

    from | to |worked
    1800 | 2400| 6

    I searched the forum earlier and could not find anything simular
    please note that the cell format for column A and B is custom 0000 in case values like 0100 have to be entered

    the formula placed in c1 is:
    =(INT(a1/100)+((a1-INT(a1/100)*100)/60))-(INT(B1/100)+((B1-INT(B1/100)*100)/60))

  • #2
    Re: Time As Text Calculations

    =VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2))+(VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))>VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2)))-VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))

    Where B1 is the "To" time. Format result as required.

    See Excel Date and Times
    Last edited by Dave Hawley; December 14th, 2006, 15:39.

    Comment


    • #3
      Re: Time As Text Calculations

      Hi,

      Try,

      =--TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)

      Format the cell as hh:mm

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Time As Text Calculations

        See also Mask Time Entries.

        Comment


        • #5
          Re: Time As Text Calculations

          KK is there any reson for the double -- and the 0 in your formula.

          Originally posted by Krishnakumar
          Hi,
          =--TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)
          HTH

          Comment


          • #6
            Re: Time As Text Calculations

            both of them converts text value to number value

            =TEXT(B1,"00\:00")+0-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)

            or

            =--TEXT(B1,"00\:00")-(--TEXT(A1,"00\:00"))+--(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00"))
            Last edited by Krishnakumar; December 14th, 2006, 20:09.
            Kris

            ExcelFox

            Comment


            • #7
              Re: Calculate Military Times Without Colon

              This seems similar ..... found it at timecardworkbook.com - if the 4 digit military time is in cell A1 and this formula is in B1:


              =IF(A1="",0,+VALUE(+CONCATENATE(+(A1-+RIGHT(A1,2))/100,":",+RIGHT(A1,2))))this worked for me!Thanks for the forum ....

              Comment


              • #8
                Re: Calculate Military Times Without Colon

                Hi
                Another version which might be of helpl! =(--(LEFT(B1,2)&":"&RIGHT(B1,2)))-(--(LEFT(A1,2)&":"&RIGHT(A1,2)))

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X