Military Time & Colon

[cottage6]

Hi All,
A user here would like to be able to key military hours, say 1500, and have
the cell format to 15:00. She doesn't want to type the colon. I have the
cell formatted as Time 13:30:55 which works great to show her military time.
Is there a way to do this? TIA and have a good day!

 

[vezerid]

The following VBA event procedure will convert the typed entry into
time. If the user types 1500 the cell will then store and display
15:00:00 (if formatted as hh:mm:ss - you can change it to hh:mm only,
since she only types up to minute precision)
The macro will work only if time is entered in cell A1 and assumes
that only time will be entered there. The If needs modification to
allow, e.g. this to happen for all cells in column A:A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then '<<--- this test might need to change
for other cells, more info needed.
hr = Int(Target.Value / 100)
mn = Target.Value Mod 100
Application.EnableEvents = False
Target.Value = hr / 24 + mn / 1440
Application.EnableEvents = True
End If
End Sub

To install:
Right click on the sheet tab.
Choose View Code...
Paste the above code to the code window in the VBA IDE.

HTH
Kostis Vezerides

 

[cottage6]

Awesome! I'm not sure what cells the user would need to format this way, but
I'll assume many entries in several columns. She has a Start Shift, End
Shift, Start Lunch and End Lunch she would like to be able to do this in, so
that's 4 columns. Below is your modified code with range c3:c20 which works
fine. How could I change this to include extra columns? I tried changing
this a bit but it didn't work.


Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("c3:c20")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
With Target

hr = Int(Target.Value / 100)
mn = Target.Value Mod 100
Application.EnableEvents = False
Target.Value = hr / 24 + mn / 1440
Application.EnableEvents = True

End With
End Sub

 

[Gord Dibben]

If Application.Intersect(Target, Range("C:F")) for contiguous full columns

If Application.Intersect(Target, Range("C3:C30, F5:F56, G3:K20")) for

non-contiguous ranges


Gord Dibben MS Excel MVP