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

Boeretom

New Member
Joined
Sep 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
130
2ID Number as TextDate of BirthResultsYear of BirthID Formatted as NUMBERDate of BirthResultsYear of BirthResultsShould BeIf this value is 0, all the 20?? dates are displayed as dates in the 1900's
3510414010508614/04/1951Correct1951510414010508614/04/1951Correct1951Correct1951If 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
4480602503008702/06/1948Correct1948480602503008702/06/1948Correct1948Correct1948
5561221503907621/12/1956Correct1956561221503907621/12/1956Correct1956Correct1956If 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
6041115504207915/11/2004Correct2004041115504207925/12/1941Wrong1941Wrong2004
7191225012508925/12/2019Correct2019191225012508925/12/2019Correct2019Correct2019If the value in this cell is 20, dates up to 2019 are calculated correctly but later dates are incorrect
8000114503008914/01/2000Correct2000000114503008903/09/2014Wrong2014Wrong2000
9210222010508922/02/2021Correct2021210222010508922/02/2021Correct2021Correct2021The 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.
10240411504007711/04/2024Correct2024240411504007711/04/2024Correct2024Correct2024
11260309020405809/03/2026Correct2026260309020405809/03/2026Correct2026Correct2026
12
13Format 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
RangeFormula
B3:B11,F3:F11B3=DATE(IF(LEFT(A3,2)+0<$K$1,20,19)&LEFT(A3,2),MID(A3,3,2),MID(A3,5,2))
D3:D11,H3:H11D3=YEAR(B3)


Any help will be greatly appreciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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)...
Excel Formula:
=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.
 
Last edited:
Solution

Boeretom

New Member
Joined
Sep 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top