# 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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### jtakw

##### Well-known Member
Hi,

May be like this:

Book3.xlsx
AB
14708040083 and8/4/1947
203061650186/16/2003
Sheet904
Cell Formulas
RangeFormula
B1:B2B1=DATE(IF(LEFT(A1,2)+0<=20,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

#### brianfosterblack

##### Board Regular
Hi,

May be like this:

Book3.xlsx
AB
14708040083 and8/4/1947
203061650186/16/2003
Sheet904
Cell Formulas
RangeFormula
B1:B2B1=DATE(IF(LEFT(A1,2)+0<=20,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))
Thank you, This works perfectly

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

#### Rick Rothstein

##### MrExcel MVP

(Assuming the values in Column A are Text (not real numbers), here is another formula that should work...
Excel Formula:
``=0+TEXT(19+(0+LEFT(A1,2)<21)&LEFT(A1,6),"0000-00-00")``
Note: You will have to format the cells containing this formula with the date format of your choice.

#### brianfosterblack

##### Board Regular
(Assuming the values in Column A are Text (not real numbers), here is another formula that should work...
Excel Formula:
``=0+TEXT(19+(0+LEFT(A1,2)<21)&LEFT(A1,6),"0000-00-00")``
Note: You will have to format the cells containing this formula with the date format of your choice.
Thanks for this Dave. The values are in Text format in order to have the first number as a zero.
(Assuming the values in Column A are Text (not real numbers), here is another formula that should work...
Excel Formula:
``=0+TEXT(19+(0+LEFT(A1,2)<21)&LEFT(A1,6),"0000-00-00")``
Note: You will have to format the cells containing this formula with the date format of your choice.
Hi Dave, An issue has come up with this formula. The number 21 is used in the formula because the current year is 2021. I have the current year entered in cell F3 of my workbook and I would like to set this formula to pick up the right 2 numbers of the year and use that instead of a fixed number of 21. I tried
Excel Formula:
``Right(\$F\$3,2)``
in place of the number 21 but that puts a century onto my date. The year is in number format. Can you assist with this issue?

#### jtakw

##### Well-known Member

Who's Dave?

Is this a continuation of your OP?
Or is this a new question?
What's in F3?
And How does F3 play a part in the OP question?

#### Rick Rothstein

##### MrExcel MVP
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")``

#### jtakw

##### Well-known Member
Thought may be you had a nick name I didn't know about

Anyway, updated my formula.
B1 uses F3 (assuming it contains the current year)
C1 hard-coded

Book3.xlsx
ABCDEF
14708040083 and8/4/19478/4/1947
203061650186/16/20036/16/2003
32021
Sheet904
Cell Formulas
RangeFormula
B1:B2B1=DATE(IF(LEFT(A1,2)+0<=RIGHT(F\$3,2)+0,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))
C1:C2C1=DATE(IF(LEFT(A1,2)+0<=TEXT(TODAY(),"yy")+0,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

#### SpillerBD

##### Well-known Member
Update your system settings for how two-digit years are interpreted.
Then use Text-to-Columns

Replies
5
Views
161
Replies
7
Views
614
Replies
4
Views
172
Replies
2
Views
149
Replies
5
Views
379

1,129,418
Messages
5,636,158
Members
416,903
Latest member
Sanjayaranj

### 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