Get year from personal number (10 digits)

[Excel43]

Hi, how can I get the birth date (YYYY-MM-DD) from a personal number that is just 10 digits?

The personal number is like this: YYMMDDXXXX (9001121515). It has no spaces or anything, just 10 digits.

I have this formula and it works if the person is born before 2000. But for 2000 and afterwards it shows "1900-" instead of "2000". For example, a person with the personal number "2101022222" is shown as "1901-01-02".

The formula right now (D3 is the cell containing the personal number):

=DATE(LEFT(D3;2);MID(D3;3;2);MID(D3;5;2))

I cant change the personal number digits, so I have to come up with a solution . Any idea?

 

[jmacleary]

Hi there. Try this. The 30 in the formula represents what the earliest date is expected (i.e. 1930), So any number that starts with 30 or more will be assumed to be 19xx. It will of course stop working properly for the year 2030 onwards, but you may choose a higher 'cutoff' if that happens.

Book2
	D
2
3	2101022222
4
5
6	2021-01-02
Sheet1

Cell Formulas
Range	Formula
D6	D6	=DATE(IF(--LEFT(D3,2)>30,"19","20")&LEFT(D3,2),MID(D3,3,2),MID(D3,5,2))

 

[Excel43]

Thanks a lot! This really helped

 

[jmacleary]

You're welcome and thanks for the feedback. A delayed-action Millenium Bug!

 

[Phuoc]

Another way:

=--TEXT(IF(D3<3100000000,20,19)&LEFT(D3,6),"0000-00-00")