"Don Guillett" wrote:

A formula
=TEXT(LEFT(a2,2)&":"&RIGHT(a2,2),"hh:mm")

Wasn't sure how to use the above but when copied & pasted into the cell it
is immediately replaced by whatever I then type into the cell.

So...I tried your next suggestion. I had to replace the Target.Column test
w/13 instead of 1 as "M" is the column in question. It works beautifully
except for values less than 1000. Entering 0959 in a cell appears as 23:59.
Entering 0500 appears as 02:00. Entering 0345 appears as 10:45. I can see no
pattern to the results nor do I see anything in your text which would change
the values entered.

Try this with column A preformatted as TEXT. Right click sheet tabview
codeinsert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
Application.EnableEvents = False
Target = Format(Left(Target, 2) & ":" & Right(Target, 2), "hh:mm")
Application.EnableEvents = True
End Sub
--
Don Guillett
SalesAid Software

"Deuxdad" wrote in message
...
Is there any way to input a 4 digit number in the range 0000 to 2359
(represents time-of-day) and have Excell automatically insert the colons,
ie
00:00? The cells are currently formatted as CUSTOM HH:MM. Forcing the user
to
have to move the hand from the number pad to the alpha characters simply
to
include the colon is annoying and inefficient.

I'm using MS Excell 2003 and my knowledge of cell formatting is extremely
limited.

TIA to all who may reply,
Deuxdad