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 can't work out how to do this. Just setting the Cell number format to "hh:mm" doesn't do it: it requires the colon to be typed, otherwise it interprets the input bizarrely and only displays 00:00.
I would appreciate any suggestions.
Many thanks,
Colin
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
If you want to enter and display 24-hour times without any punctuation (e.g., “0900” instead of “09:00”), you can use a custom number format to display the time values in the desired format.
Here’s how you can do it:
- Select the cells where you want to enter and display the time values.
- On the Home tab, in the Number group, click the drop-down arrow next to the Number Format box and select More Number Formats.
- In the Format Cells dialog box, select Custom from the Category list.
- In the Type box, enter “0000” (without the quotes) and click OK.
Now, when you enter a time value in one of the selected cells without any punctuation (e.g., “0900”), it will be displayed as a valid 24-hour time without a colon (e.g., “0900”).
Note that this method only changes how the time values are displayed, not how they are stored internally by Excel. The time values are still stored as decimal numbers representing fractions of a day, so you can use them in calculations and formulas as usual.
Hope this helps!
