Excel Help

Discussion in 'Chit Chat' started by TraderJoe08, Feb 9, 2010.

  1. i don't know anything about programming, but i think this is an easy fix.

    Sub ConvertTimes()

    'Converts times in the text format "809a" into Excel time format
    'Processes all selected cells on the active worksheet.

    Dim C As Range 'a cell
    Dim HH As String
    Dim MM As String
    Dim L As Integer 'the string length

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each C In Selection.Cells
    'Text string must end with A or P
    If UCase(C.Value) Like "*[AP]" Then
    L = Len(C.Value)
    MM = Mid(C.Value, L - 2, 2)
    HH = Left(C.Value, L - 3)
    If HH = "12" Then HH = "0"
    C.Value = (IIf(Right(UCase(C.Value), 1) = "P", CInt(HH) + 12, CInt(HH)) + CInt(MM) / 60) / 24#
    C.NumberFormat = "h:mm;@"
    End If
    Next C

    End Sub

    my data doesn't have an "a" or "p" just need to convert 1245 to 12:45
    data is already in military time

    thanks :) i really appreciate answers
  2. Here's a real simple way to do it. If your times are in column A for example, put this in B1
    Then copy and paste as needed down the B column.

    Oh and the B column should be formatted as hh:mm
  3. thanks

    I would like to have it converted into the same cell so i can then save the file as .txt and import it into a platform.
  4. Ok it worked, now i have another issue lol

    Thanks for your help
  5. Hey no sweat. I'm by no means an Excel guru but if you want toss the new issue out there and I'll give it shot... :)