Extract date from "number"

[Tom.Jones]

Hi,

This VBA code was written by Peter_SSs, in "Full Date of Birth YYYY-MM-DD from 6 char. String YYMMDD"

Sub Extract_Date_Text()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "@"
    .Value = Evaluate(Replace("TEXT(20-(LEFT(#,2)-45>0)&LEFT(#,6),""0-00-00"")", "#", .Offset(, -1).Address))
  End With
End Sub

@Peter_SSs
please modify the VBA code to extract data (something like AKOsman1 post) with difference that numbers in column A (starting in A2)
are like this:
1 or 2 in front of say 470207 mean year is from 1900 to 1999
3 or 4 front of say 470207 mean year is from 1800 to 1899
5 or 6 in front of say 050207 mean year is from 2000 to 2999

In column A numbers is like:
1470207 in column B will be 1947-02-07 or if is possible 07.02.1947 (european date)
5050207 in column B will be 2005-02-07 or if is possible 07.02.2005

Thank you.

 

[Scott Huish]

Hi,

This VBA code was written by Peter_SSs, in "Full Date of Birth YYYY-MM-DD from 6 char. String YYMMDD"

Sub Extract_Date_Text()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "@"
    .Value = Evaluate(Replace("TEXT(20-(LEFT(#,2)-45>0)&LEFT(#,6),""0-00-00"")", "#", .Offset(, -1).Address))
  End With
End Sub

@Peter_SSs
please modify the VBA code to extract data (something like AKOsman1 post) with difference that numbers in column A (starting in A2)
are like this:
1 or 2 in front of say 470207 mean year is from 1900 to 1999
3 or 4 front of say 470207 mean year is from 1800 to 1899
5 or 6 in front of say 050207 mean year is from 2000 to 2999

In column A numbers is like:
1470207 in column B will be 1947-02-07 or if is possible 07.02.1947 (european date)
5050207 in column B will be 2005-02-07 or if is possible 07.02.2005

Thank you.

Excel doesn't support dates before the year 1900. It would have to return those values as a text string and not an actual date value.

 

[Tom.Jones]

Excel doesn't support dates before the year 1900. It would have to return those values as a text string and not an actual date value.

It is not a problem.

 

[Peter_SSs]

Try

Sub Extract_Date_Text()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("right(#,2)&"".""&mid(#,4,2)&"".""&lookup(--left(#,1),{1,3,5},{19,18,20})&mid(#,2,2)", "#", .Offset(, -1).Address))
  End With
End Sub

Sample In/Out

Tom.Jones.xlsm
	A	B
1	In	Out
2	1470207	07.02.1947
3	5050207	07.02.2005
4	4051227	27.12.1805
Sheet2

 

[Tom.Jones]

Thank you.

 

[Peter_SSs]

You are welcome.