I remember a earlier tip (that I can't find now) about adding code to a single worksheet for special formating of time for stuff like payroll. I use it for one of my payroll worksheets, because it is a hand written time sheet and it makes entering time so much easier. On the worksheet you need this on right click on the tab and select view code at the bottom. The code I use, just change the range to the cells you need this in. This works for 24 hour time - example: enter 1700 for 5pm and it will be displayed at 17:00, but the value in the cell is 5:00:00 PM. Hope this helps. Maybe some else can find the original tip.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B2:E15")) Is Nothing Then Exit Sub

With Target

vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With

Application.EnableEvents = True

End Sub