Jul 21 2022 03:53 PM
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?
Jul 22 2022 07:47 AM
SolutionI 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
Jul 22 2022 10:15 AM
Jul 22 2022 07:47 AM
SolutionI 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