Convert a number in text to a valid date

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
189
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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))
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

(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
Joined
Nov 1, 2011
Messages
189
(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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
Yeah, I was wondering who Dave was also. o_O 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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Thought may be you had a nick name I didn't know about :unsure:

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
Joined
Jul 2, 2014
Messages
2,710
Update your system settings for how two-digit years are interpreted.
Then use Text-to-Columns
 

Watch MrExcel Video

Forum statistics

Threads
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.
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