Converting numbers into formats

[hbwhitehead]

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=203 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>122340</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64> =</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right width=75>12:23:40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>122376</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"> =</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>12/23/76</TD></TR></TBODY></TABLE>


a) If I type 122340 but want it to format automatically into 12:23:40, how can I create this conversion?

b) If I type 122376 but want it to format automatically into 12/23/76, how can create this conversion?

 

[BobUmlas]

You didn't specify how Excel is to know the difference between the 2 formats wanted -- this routine will format numbers entered in column A with the colons and in column B with the slashes.
Change to suit. -- right-click the sheet tab, select View Code, enter this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
Target.Value = Left(Target.Value, 2) & ":" & Mid(Target.Value, 3, 2) & ":" & Right(Target.Value, 2)
End If
ElseIf Target.Column = 2 Then
If IsNumeric(Target.Value) Then
Target.Value = Left(Target.Value, 2) & "/" & Mid(Target.Value, 3, 2) & "/" & Right(Target.Value, 2)
Target.NumberFormat = "mm/dd/yy"
End If
End If
Application.EnableEvents = True
End Sub

 

[bryonwoods30]

if you want it done automatically, go to format cells, then custom then type into the box

##":"##":"## or ##"/"##"/"##

this will let you type in 122376 and get 12:23:76 or 12/23/76