Automatically insert Colon

[aldeb]

I have a spreadsheet where I am just entering times such as 1620 or 0945 etc. My question is: How can I enter the time such as 1620 and have it appear as 16:30 or 09:45. I would like to have the colon automatically insert between the four digits of time without having to type in the colon. Can this be done?

 

[aldeb]

For info: Below is the code that solves this post.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rgCell As Range
Set rgCell = Target(1, 1)
If Intersect(rgCell, Range("e2:f2000")) Is Nothing Or Len(rgCell) = 0 Then Exit Sub
Application.EnableEvents = False
rgCell = Int(rgCell / 100) / 24 + (Right(rgCell, 2)) / 1440
Application.EnableEvents = True

End Sub

 

[alastair69]

you could use a formating statment by specifying your column and the time format for that cell