Extract date in mm/dd/yyy

[kumara_faith]

Hi,

I have a list of employee numbers in the following format:

770205145598

The first two numbers are the year, second two digits are the month and the next two digits are the day. So, in this case the employees' date of birth is February 5 1977. Is there a way to extract the date of birth in mm/dd/yyyy format ? Thank you in advance.

 

[kumara_faith]

Hi,

It shows as dd/mm/yyyy

 

[Rick Rothstein]

And you are saying that this formula (posted earlier by Rich and modified by Alex) does not work for you????

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1, 5,2))

It should give you a real date but showing in your native format (dd/mm/yyyy) which you can change using Cell Format to display it in whatever way you want. Are you telling us it gives you a #VALUE! error instead?

 

[MARK858]

The formula @Alex Blakenburg posted works fine for me for your sample number and my computer is set up as dd/mm/yyyy (I have wrapped it in a TEXT function just to make it display as mm/dd/yyyy).

What you you get with the formula =CODE(A1).

Can you also click the

in the XL2BB sample below and paste in cell A1 in a new sheet (format the cell as Text before doing the paste) and report back what you get (please note that cell A1 is a number formatted as text in my actual sheet, not as it appears below)

Book1
	A	B
1	791124095157	11/24/1979
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXT(DATE( LEFT(A1,2), MID(A1,3,2), MID(A1, 5,2)),"mm/dd/yyyy")

 

[kumara_faith]

Hi Rick and MARK858,

My sincere apologies. I figured out what was meant by turning it into text format and now it worked. Thank you again for your valuable time and patience.

 

[MARK858]

You're welcome from all of us