Auto insert colon and decimal period

[duffer]

I am trying to create a cross country result spreadsheet and would like to automatically insert the colon and decimal period. The time format would be,

[mm]:ss.00

So 221710 becomes 22:17.10 22 mintues, 17 seconds and 10 hundreds. It will always be a six digit entry and in column D

Thanks!

[SamT]

A six digit number with no decimals is always treated as a date with no time when using Date/Time Formats.

The only way I can think of is to use the Worksheet's Change Event to pull the number out and use VBA's string manipulation functions on it

This goes in the Worksheet's code page

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

If Not Target.Column = 4 Then Exit Sub
For Each cel In Target
  cel.Text = Left(cel, 2) & ":" & Mid(cel, 3, 2) & "." & Right(cel, 2)
Next cel
End Sub