 SOLVED

# Schedule hour input

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 (Occasional Contributor)
Solution

# Re: Schedule hour input

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``````

# Re: Schedule hour input

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!