Forum Discussion

CHarrison1968

Copper Contributor

Apr 11, 2023

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

excel

JKPieterse

Silver Contributor

Apr 11, 2023

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

Tom_Hardy980

Copper Contributor

Apr 11, 2023

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!