Formatting a 6 digit Number to a Date

[monicamarie]

I have a column of dates in the format shown below which are stored as text. I would like to use the Format>Format Special option to convert the numbers to an actual date with the year displayed first. For example, on the cell shown below, I would like to convert the number to 10/01/01.

Is this possible using the Special Format feature?

Any help would be greatly apprecitaed.

<TABLE style="WIDTH: 76pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=101 border=0><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ccffff" width=101 height=19>DATE</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>010110</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>010210</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>010310</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>020410</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>020510</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>020610</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>030710</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>030810</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>030910</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>041010</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>041110</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>041210</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>051310</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>051410</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>051510</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>061610</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>061710</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>060111</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>070211</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>070311</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>070411</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>080511</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>080611</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>080711</TD></TR></TBODY></TABLE>

 

[VoG]

Try

=DATE(2000+RIGHT(A2,2),LEFT(A2,2),MID(A2,3,2))

and format as required.

 

[Jonmo1]

Try using Data - Text to columns - Deliminated
On the 3rd screen, select Date MDY

That will convert them to real dates
Then you can format them any way you like.

 

[monicamarie]

Both of those suggestions work great. You guys are the best!

Thank you so much

Don't know what I'd do without Mr Excel