Auto-formatting cell contents
 

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

Auto-formatting cell contents
 

Deuxdad,

Visit

http://www.cpearson.com/excel/DateTimeEntry.htm

for instructions.

HTH,
Bernie
MS Excel MVP


"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

Auto-formatting cell contents
 

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

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

Auto-formatting cell contents
 

"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

Auto-formatting cell contents
 

"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

Auto-formatting cell contents
 

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

Auto-formatting cell contents
 

"Don Guillett" wrote:

preformat your column as text

That of course, worked. Must'v had my glasses on upside-down.

If I may impose again... I noticed that if I make an entry error and erase
the cell contents to correct it, the formula no longer works. If, however I
simply retype the correct entry into the cell, the formula remains
un-affected. Any idea why this may occur? It's not terribly important - I
just have to remember to always re-enter the data rather than erase the cell
contents.

--
Thanks again for the help.
Deuxdad

Auto-formatting cell contents
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 7 Then Exit Sub
ActiveCell.NumberFormat = "@"
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
...


"Don Guillett" wrote:

preformat your column as text

That of course, worked. Must'v had my glasses on upside-down.

If I may impose again... I noticed that if I make an entry error and erase
the cell contents to correct it, the formula no longer works. If, however
I
simply retype the correct entry into the cell, the formula remains
un-affected. Any idea why this may occur? It's not terribly important - I
just have to remember to always re-enter the data rather than erase the
cell
contents.

--
Thanks again for the help.
Deuxdad