Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting a cell the same as the source cell from a lookup table | Excel Discussion (Misc queries) | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
Using contents of a cell in a formula | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
cell formatting problems | New Users to Excel |