Announcement

Collapse
No announcement yet.

Convert date and time to military time

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

  • Convert date and time to military time

    Hello,

    Hope some can help me out with this issue.

    I have a column in a spread sheet that is populated with date/time from an external application in the format "mm/dd/yyyy hh:mm" for example value in cell "B1" is "5/7/2011 15:45". I need only 1545. Using excel formula, I am able to convert in the adjacent cell "C1" using the formula =text(B1,"hhmm").

    I would however like to replace the contents in the original column "B:B" to hhmm using vba code. Below is the code that I have come up with but it does not seem to work. The macro returns #NAME? throughout column B.

    Thanks for any help in advance.


    Code:
    Sub arrivalTimes()
    
    
    Dim sta As String
    
    
    With ActiveSheet
    Range("C1").Select
    Do
    sta = "=text(ActiveCell, ""hhmm"")"
    ActiveCell.Value = sta
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 0))
    End With
    
    
    
    
    End Sub

  • #2
    Re: Convert date and time to military time

    For instance:

    Code:
    Sub arrivalTimes()
        Columns(2).NumberFormat = "hhmm"
        For Each Rng In Columns(2).SpecialCells(2, 1)
            Rng.Value = Rng.Value - Int(Rng.Value)
        Next
    End Sub
    Watch out, it will overwrite values in column B. Take a backup of the file before testing.
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Convert date and time to military time

      Dear Wigi,

      Thank you for the code. I have just tried it and get a "Run Time error 1004" "no cells were found".
      Also I noticed in the code no Dim statements. Is that the reason for the error? im not sure as im a novice at VBA. thanks in advance for your time.

      Regards,
      XL Factor

      Comment


      • #4
        Re: Convert date and time to military time

        Change
        Code:
        sta = "=text(ActiveCell, ""hhmm"")"
        to
        Code:
        sta = Format$(ActiveCell, "hhmm")
        or
        Code:
        sta = Evaluate("text(" & ActiveCell.Address & ", ""hhmm"")")

        Comment


        • #5
          Re: Convert date and time to military time

          Dear Jindon,

          Thank you for your quick response. I have tried both of your above suggestions, but they dont work. The code changes all the dates to a different date and all the times to 0:00 in column B. Thanks again for all your help, I really appreciate it.

          Regards,
          XL Factor.

          Comment


          • #6
            Re: Convert date and time to military time

            If the value in the cell is serial date with time then it should work.

            I have tested here.

            Comment


            • #7
              Re: Convert date and time to military time

              Try expanding the columns widths.... if the 'dates' align left then they're strings and you'll need to do more processing. If they align right then Jindons' solution should work.

              Comment


              • #8
                Re: Convert date and time to military time

                Dear Jindon,

                I am attaching a sample workbook with the data and code in it. I am unable to get it to work. Please note the date/time column in the attachment is in column C in the attachment. Any help is greatly appreciated.
                Thank you again in advance for your time and efforts.

                Regards,
                XL Factor.
                Attached Files

                Comment


                • #9
                  Re: Convert date and time to military time

                  try
                  Code:
                  sta = Format$(ActiveCell, "hhmm")
                  Activecell.numberformat = "@"
                  ActiveCell = sta
                  Or you could do it with no loop
                  Code:
                  Sub test()
                  Dim x
                  With Range("c1", Range("c" & Rows.Count).End(xlUp))
                      x = Evaluate("if(" & .Address & "<>"""",text(" & .Address & ",""hhmm""),"""")")
                      .NumberFormat = "@"
                      .Value = x
                  End With
                  End Sub

                  Comment

                  Working...
                  X