Date of Birth to proper date format

[jschrock]

Hello!

I have data formatted like this:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]51767[/TD]
[/TR]
[TR]
[TD="class: xl66"]72236[/TD]
[/TR]
[TR]
[TD="class: xl66"]112380[/TD]
[/TR]
[TR]
[TD="class: xl66"]100886[/TD]
[/TR]
[TR]
[TD="class: xl66"]42466[/TD]
[/TR]
[TR]
[TD="class: xl66"]100665[/TD]
[/TR]
</tbody>[/TABLE]

The problem is that these are Dates of Birth and I need them to look like:

05/17/67
07/22/36
11/23/80
10/08/86
04/24/66
10/06/65

Any help would be great!

 

[Joe4]

Here is one formula that will do that (for an entry in cell A1):

=DATE(RIGHT(A1,2),LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2))

and then just use the "mm/dd/yy" custom date format.

 

[jschrock]

That's the ticket!!! THANK YOU SO MUCH!

JTS

 

[Joe4]

You are welcome.

 

[Scott Huish]

A shorter way:

=TEXT(A1,"00\/00\/00")+0

Format as date.

Excel 2010
	A	B
1	51767	5/17/1967
2	72236	7/22/1936
3	112380	11/23/1980
4	100886	10/8/1986
5	42466	4/24/1966
6	100665	10/6/1965
Sheet1

Cell Formulas
Range	Formula
B1	=TEXT(A1,"00\/00\/00")+0
B2	=TEXT(A2,"00\/00\/00")+0
B3	=TEXT(A3,"00\/00\/00")+0
B4	=TEXT(A4,"00\/00\/00")+0
B5	=TEXT(A5,"00\/00\/00")+0
B6	=TEXT(A6,"00\/00\/00")+0

 

[jschrock]

How does this work? Like if I was going to explain this to someone?

Thanks!

 

[Scott Huish]

It's using the text function to force it into a 6 digit format separated by /. The \ tells the text format to take the next character literally. But this returns a text string, adding 0 to it coerces it back into a number.

 

[jschrock]

Awesome. Thanks for the explanation!!!

 

[jschrock]

Last question.

Why does this function want to change on of the DOBs from:

70629 to 7/06/2029? How can I get it to do 1929?

 

[Joe4]

See: https://support.microsoft.com/en-my/help/214391/how-excel-works-with-two-digit-year-numbers