SOLVED

Schedule hour input

Copper Contributor

Hi,

 

Is the a way to input ex: 230p and convert to 2:30 PM or 8a into 8:00 AM, in excel using formula or VBA?

2 Replies
best response confirmed by JonathanSantos (Copper Contributor)
Solution

@JonathanSantos 

I assume that my reply in Excel Time Formatting wasn't worth acknowledging...

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim v As Long
    Dim h As Long
    Dim m As Long
    If Not Intersect(Range("B2:B10,D2:D10"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("B2:B10,D2:D10"), Target)
            If rng.Value Like "*a" Then
                v = Val(rng.Value)
                If v <= 12 Then
                    h = v
                    m = 0
                Else
                    h = v \ 100
                    m = v Mod 100
                End If
                If h = 12 Then h = 0
                rng.Value = TimeSerial(h, m, 0)
            ElseIf rng.Value Like "*p" Then
                v = Val(rng.Value)
                If v <= 12 Then
                    h = v
                    m = 0
                Else
                    h = v \ 100
                    m = v Mod 100
                End If
                If h < 12 Then h = h + 12
                rng.Value = TimeSerial(h, m, 0)
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
The other reply was good but not exactly what I was looking for, but this one is perfect this is exactly what I needed, I really appreciate!
1 best response

Accepted Solutions
best response confirmed by JonathanSantos (Copper Contributor)
Solution

@JonathanSantos 

I assume that my reply in Excel Time Formatting wasn't worth acknowledging...

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim v As Long
    Dim h As Long
    Dim m As Long
    If Not Intersect(Range("B2:B10,D2:D10"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("B2:B10,D2:D10"), Target)
            If rng.Value Like "*a" Then
                v = Val(rng.Value)
                If v <= 12 Then
                    h = v
                    m = 0
                Else
                    h = v \ 100
                    m = v Mod 100
                End If
                If h = 12 Then h = 0
                rng.Value = TimeSerial(h, m, 0)
            ElseIf rng.Value Like "*p" Then
                v = Val(rng.Value)
                If v <= 12 Then
                    h = v
                    m = 0
                Else
                    h = v \ 100
                    m = v Mod 100
                End If
                If h < 12 Then h = h + 12
                rng.Value = TimeSerial(h, m, 0)
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

View solution in original post