Convert a number in text to a valid date

[brianfosterblack]

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

 

[jtakw]

Hi,

May be like this:

Book3.xlsx
	A	B
1	4708040083 and	8/4/1947
2	0306165018	6/16/2003
Sheet904

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(IF(LEFT(A1,2)+0<=20,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

 

[brianfosterblack]

Hi,

May be like this:

Book3.xlsx
	A	B
1	4708040083 and	8/4/1947
2	0306165018	6/16/2003
Sheet904

Cell Formulas
Range	Formula
B1:B2	B1	=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]

You're welcome, thanks for the feedback.

 

[Rick Rothstein]

(Assuming the values in Column A are Text (not real numbers), here is another formula that should work...

=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]

(Assuming the values in Column A are Text (not real numbers), here is another formula that should work...

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

=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

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]

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]

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

=0+TEXT(19+(0+LEFT(A1,2)<0+TEXT(NOW(),"yy"))&LEFT(A1,6),"0000-00-00")

 

[jtakw]

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
	A	B	C	D	E	F
1	4708040083 and	8/4/1947	8/4/1947
2	0306165018	6/16/2003	6/16/2003
3						2021
Sheet904

Cell Formulas
Range	Formula
B1:B2	B1	=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:C2	C1	=DATE(IF(LEFT(A1,2)+0<=TEXT(TODAY(),"yy")+0,2000,1900)+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

 

[SpillerBD]

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