# Convert a number in text to a valid date

#### brianfosterblack

##### Board Regular
Here is a sample of 2 identity numbers where the first 6 numbers are the date of birth YYmmDD,
The left 2 numbers are the year of birth, the next 2 are the month and the next 2 are the day.
Anyone where the left 2 numbers are between 00 and 20 were born from 2000 onwards
All others were born in the 1900s - So not one of the people in this list is older than 99
I need to have a formula to convert this to a valid date of birth DDmmYYYY

I have tried Date(YY,mm,DD) but that gives me all the birth dates in the 1900s.

Can anyone help.

4708040083 and
 0306165018

#### brianfosterblack

##### Board Regular
Yeah, I was wondering who Dave was also. Anyway, for the formula I posted, this will assume numbers between 00 and the last two digits of the current year to be in the 2000s, all others will be assume to be in the 1900s...
Excel Formula:
=0+TEXT(19+(0+LEFT(A1,2)<0+TEXT(NOW(),"yy"))&LEFT(A1,6),"0000-00-00")
Sorry Rick, it was very late at night here in South Africa, so excuse the blonde moment. Thank you for the resolution to this problem, it works perfectly now. Thank you both for the help.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Replies
7
Views
73
Replies
5
Views
170
Replies
7
Views
631
Replies
4
Views
174
Replies
2
Views
164

1,130,435
Messages
5,642,104
Members
417,256
Latest member
JessAw

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back