none
Converting 1200 to 12:00 on a spreadsheet.

    General discussion

  • I'm trying to use a macro to convert a 4 digit entry by my spreadsheet user into time format hh:mm, so for example 1200 into 12:00, but only on certain ranges of cells.  So far i've got the following script on the code for the excel sheets i want to use it on, as oppose to in a module, but it doesn't seem to make any difference.  Can anyone help?

    Sub Fixhhmm()
    Dim cell As Range
    Selection.NumberFormat = "hh:mm"
    On Error Resume Next
    For Each cell In Selection
      If Len(cell) = 4 Then
         cell.Value = TimeSerial(Left(cell.Value, 2), _
           Right(cell.Value, 2), 0)
      End If
    Next cell
    End Sub


    Sub FixhhmmV()
    Dim cell As Range
    Dim vValue As Single
    On Error Resume Next
    For Each cell In Selection
      If InStr(1, cell.NumberFormat, ":") = 0 Then
        If cell.Value > 1 Then    'Not a time serial yet
           cell.Value = TimeSerial(Int(cell.Value / 100), _
             Int(cell.Value - 100 * Int(cell.Value / 100)), 0)
        End If
      End If
    Next cell
    Selection.NumberFormat = "[hh]:mm"
    End Sub

    Tuesday, May 31, 2011 7:35 PM

All replies

  • Scrap that, i've done it.  Here's what i've done in case anyone else is looking to do simular.  Your cells must be formatted to hh:mm and edit the target range to the cells you need:

    rivate Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim TimeStr As String

    On Error GoTo EndMacro
    If Application.Intersect(Target, Range("B10:C49,G10:H49,L10:M49,Q10:R49,V10:W49,AA10:AB49,AF10:AG49")) Is Nothing Then
        Exit Sub
    End If
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    If Target.Value = "" Then
        Exit Sub
    End If

    Application.EnableEvents = False
    With Target
    If .HasFormula = False Then
        Select Case Len(.Value)
            Case 1 ' e.g., 1 = 00:01 AM
                TimeStr = "00:0" & .Value
            Case 2 ' e.g., 12 = 00:12 AM
                TimeStr = "00:" & .Value
            Case 3 ' e.g., 735 = 7:35 AM
                TimeStr = Left(.Value, 1) & ":" & _
                Right(.Value, 2)
            Case 4 ' e.g., 1234 = 12:34
                TimeStr = Left(.Value, 2) & ":" & _
                Right(.Value, 2)
            Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
                TimeStr = Left(.Value, 1) & ":" & _
                Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
            Case 6 ' e.g., 123456 = 12:34:56
                TimeStr = Left(.Value, 2) & ":" & _
                Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
            Case Else
                Err.Raise 0
        End Select
        .Value = TimeValue(TimeStr)
    End If
    End With
    Application.EnableEvents = True
    Exit Sub
    EndMacro:
    MsgBox "You did not enter a valid time"
    Application.EnableEvents = True
    End Sub

    Tuesday, May 31, 2011 8:15 PM