Dates in 2000's displayed as dates in 1900's

[Boeretom]

In South Africa each persons ID Number consists of 13 digits. The first 6 being your date of birth, as an example - if you were born on the 6th of June 1948, your ID will start with 480606. To get your year of birth, I can use various Excel Functions to convert these six digits to a legitimate date and then get your year of birth from this date using the YEAR Function. The problem is if someone was born on third of February 2000 your ID will start with with000203. This causes a big problem in Excel as Excel sometimes thinks you were born on third February 1900. This is even a bigger problem if you format the number as a number using the '000###' format. The problem is not that bad if you format the number as TEXT, starting each ID Number with an apostrophe, however as various people have to fill in these numbers they do not always remember the apostrophe. I received an answer from Mike at ExceIsFun and Alan at ComputerGaga. I have used their input to create the enclosed worksheet. Is there a way to solve this problem that will make it easy to use for people that are not very good in Excel and mainly only do data capturing from printed forms or other Excel worksheets. Here is my current solution.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1											30
2	ID Number as Text	Date of Birth	Results	Year of Birth	ID Formatted as NUMBER	Date of Birth	Results	Year of Birth	Results	Should Be		If this value is 0, all the 20?? dates are displayed as dates in the 1900's
3	5104140105086	14/04/1951	Correct	1951	5104140105086	14/04/1951	Correct	1951	Correct	1951		If this value is 1 to 4, the 2000 dates are displayed correctly, while all dates from 2001 onwards are displayed as dates in the 1900's
4	4806025030087	02/06/1948	Correct	1948	4806025030087	02/06/1948	Correct	1948	Correct	1948
5	5612215039076	21/12/1956	Correct	1956	5612215039076	21/12/1956	Correct	1956	Correct	1956		If this value is 5 to 19, the 2002 to 2019 dates are displayed correctly, while all dates from 2020 onwards displayed as dates in the 1900's
6	0411155042079	15/11/2004	Correct	2004	0411155042079	25/12/1941	Wrong	1941	Wrong	2004
7	1912250125089	25/12/2019	Correct	2019	1912250125089	25/12/2019	Correct	2019	Correct	2019		If the value in this cell is 20, dates up to 2019 are calculated correctly but later dates are incorrect
8	0001145030089	14/01/2000	Correct	2000	0001145030089	03/09/2014	Wrong	2014	Wrong	2000
9	2102220105089	22/02/2021	Correct	2021	2102220105089	22/02/2021	Correct	2021	Correct	2021		The value in this cell must therefor always be one number higher than your current year. The best is just to enter a value here that would accommodate dates for maybe the next 10 years say 30.
10	2404115040077	11/04/2024	Correct	2024	2404115040077	11/04/2024	Correct	2024	Correct	2024
11	2603090204058	09/03/2026	Correct	2026	2603090204058	09/03/2026	Correct	2026	Correct	2026
12
13	Format your ID Numbers as text, starting with an apostrophe and not as a number using special formatting like 000##########. As all numbers starting with 0 will return incorrect data.
14
15
Sheet1

Cell Formulas
Range	Formula
B3:B11,F3:F11	B3	=DATE(IF(LEFT(A3,2)+0<$K$1,20,19)&LEFT(A3,2),MID(A3,3,2),MID(A3,5,2))
D3:D11,H3:H11	D3	=YEAR(B3)

Any help will be greatly appreciated.

 

[Rick Rothstein]

I'm sure there must be a shorter formula that this, but it does work (the assumption is no one is ever more than 100 years old)...

=0+(IF(0+LEFT(RIGHT("0000"&A3),2)<MOD(YEAR(NOW()),100),20,19)&TEXT(LEFT(RIGHT("0000"&A3,13),6),"00-00-00"))

This formula returns the serial date so you will have to format the cell in order for it to display in a date format.

 

[Boeretom]

Thanks Rick, this formula works great for all dates of birth up to today. Will be able to use this. Your help in this matter is much appreciated.