Forum Discussion
Entering 24hr time values
Hi, My user wants to be able to enter 24hr times as "0900", "1245" without any other punctuation and have them convert to (or at least, be interpreted as) valid 24hr times (e.g. 09:00, 12:45). I ...
CHarrison1968 You can do this with a bit of VBA code. RIght-click the sheet tab and choose View Code. Paste in this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Variant
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub 'Only do this for column A
If Target.Cells.Count = 1 Then
val = Target.Text
If InStr(val, ":") > 0 Then Exit Sub 'Already has colon
val = Format(val, "0000")
val = Left(val, 2) & ":" & Right(val, 2)
Application.EnableEvents = False
Target.Value = val
Application.EnableEvents = True
End If
End Sub
- It seems to stop working after it encounters the first data entry value already containing a colon. Could you check this?
I am not fluent in VB (or Excel for that matter) so it may be a user error.
I am interested in adapting this to achieve a format that looks like
MM/DD HH24:MI
where the user only enters numbers and the slash, space, and colon are all inserted dynamically.
Thanks! - Thanks Jan. I should have specified in my original post - unfortunately VBA is not an option (client's decision - not mine)
CHarrison1968 ok, fair enough. Perhaps using autocorrect is an option. Let an infrequently used combination of two characters that are easy to type on any keyboard be replaced with : that way the user can type something like 12++30 and autocorrect changes that to 12:30
