preformat your column as text


--
Don Guillett
SalesAid Software

"Deuxdad" wrote in message
...


"Deuxdad" wrote:



"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


Please ignore previous reply. I tried it on a clean worksheet and it works
for any 4 digit number flawlessly. Obviously there's something within my
existing worksheet that's messing it up.

Thanks again for the help.
Deuxdad