Full Date of Birth YYYY-MM-DD from 6 char. String YYMMDD

A

AKOsman1

New Member
Joined
Nov 19, 2023
Messages
24
Office Version
  1. 2007
Platform
  1. Windows
I have a 13 Character ID No. The first 6 characters represent the Date of Birth (YYMMDD). I want to display the date as YYYY-MM-DD eg;
470527 = 1947-05-27
500113 = 1950-01-13
000630 = 2000-06-30
050114 = 2005-01-14
Please show me how to achieve this.
 
Sort by date Sort by votes
AKOsman1 said:
My sincere apologies. I forgot to mention in my initial request that I wanted the solution in Excel Vba code. I only realized my error after I received your answer in Excel only.
Click to expand...
Fair enough - but what about my point 2? One result is a text result, one result is an actual date (number) result
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Both give the same result. I can use either to display the full date of birth (YYYY-MM-DD)
 
Upvote 0
AKOsman1 said:
I can use either to display the full date of birth (YYYY-MM-DD)
Click to expand...
OK, so give this a try with a copy of your worksheet. It assumed original text in column A starting in A2 with results in column B.

VBA Code: 
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
 
Upvote 0
The following is the result: It is always using A2 to calculate the OUTDATE
INDATEOUTDATE
4705271947-05-27
4805271947-05-27
4905271947-05-27
5005271947-05-27
5105271947-05-27
5205271947-05-27
5305271947-05-27
5405271947-05-27
5505271947-05-27
5605271947-05-27
5705271947-05-27
5805271947-05-27
5905271947-05-27
6005271947-05-27
6105271947-05-27
6205271947-05-27
6305271947-05-27
6405271947-05-27
6505271947-05-27
6605271947-05-27
6705271947-05-27
6805271947-05-27
6905271947-05-27
7005271947-05-27
7105271947-05-27
7205271947-05-27
7305271947-05-27
7405271947-05-27
7505271947-05-27
7605271947-05-27
7705271947-05-27
7805271947-05-27
7905271947-05-27
8005271947-05-27
8105271947-05-27
8205271947-05-27
8305271947-05-27
8405271947-05-27
8505271947-05-27
8605271947-05-27
8705271947-05-27
8805271947-05-27
8905271947-05-27
9005271947-05-27
9105271947-05-27
9205271947-05-27
9305271947-05-27
9405271947-05-27
9505271947-05-27
9605271947-05-27
9705271947-05-27
9805271947-05-27
0005271947-05-27
0105271947-05-27
0205271947-05-27
0305271947-05-27
0405271947-05-27
0505271947-05-27
0605271947-05-27
0705271947-05-27
0805271947-05-27
0905271947-05-27
1005271947-05-27
1105271947-05-27
1205271947-05-27
1305271947-05-27
1405271947-05-27
1505271947-05-27
1605271947-05-27
1705271947-05-27
1805271947-05-27
1905271947-05-27
2005271947-05-27
2105271947-05-27
2205271947-05-27
2305271947-05-27
2405271947-05-27
2505271947-05-27
2605271947-05-27
2705271947-05-27
2805271947-05-27
2905271947-05-27
3005271947-05-27
3105271947-05-27
3205271947-05-27
3305271947-05-27
3405271947-05-27
3505271947-05-27
3605271947-05-27
3705271947-05-27
3805271947-05-27
3905271947-05-27
4005271947-05-27
4105271947-05-27
4205271947-05-27
4305271947-05-27
4405271947-05-27
4505271947-05-27
4605271947-05-27
 
Upvote 0
That is not happening for me. Here are my results (first 30 rows) after using what you posted in column A.
Did you modify the code in any way?
Do you have any other vba code in the workbook?

AKOsman1.xlsm
AB
1INDATEOUTDATE
24705271947-05-27
34805271948-05-27
44905271949-05-27
55005271950-05-27
65105271951-05-27
75205271952-05-27
85305271953-05-27
95405271954-05-27
105505271955-05-27
115605271956-05-27
125705271957-05-27
135805271958-05-27
145905271959-05-27
156005271960-05-27
166105271961-05-27
176205271962-05-27
186305271963-05-27
196405271964-05-27
206505271965-05-27
216605271966-05-27
226705271967-05-27
236805271968-05-27
246905271969-05-27
257005271970-05-27
267105271971-05-27
277205271972-05-27
287305271973-05-27
297405271974-05-27
307505271975-05-27
Sheet2
 
Upvote 0
I did not modify the code in any way.
This is the code I am using which is resident in "ThisWorkbook"
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

There is no other code in "ThisWorkbook"
 
Upvote 0
AKOsman1 said:
is resident in "ThisWorkbook"
Click to expand...
I would have it in a standard module rather than the ThisWorkbook module but as far as I can see that is not related to the problem.
I cannot reproduce the results that you are reporting so I'm afraid I don't have a further suggestion other than to try creating a new workbook to test to see if the same thing happens.
 
Upvote 0
I initially had the code in a standard module. When it gave the incorrect results I removed it from the standard module and copied your code into "ThisWorkbook" but got the same incorrect result.
I now created a new project and got the same incorrect result.
I have some code in the "Personal" workbook. Would this have any effect. If not I would like to make another suggestion:
You can create a "Function" which receives a 13 character ID. No. (YYMMDDnnnnnnn). The first 6 characters are the date of birth as String. Convert this to YYYY-MM-DD as String using your code and return the answer. In this case there is a single Input and a single Output.
I hope I am making sense.
 
Upvote 0
I do not normally comment on VBA or make VBA suggestions.
You can edit the formula to show actual dates and not text.
You could then try formatting the results different ways to ensure the Dates are real dates.
It is possible that Excel 2007 does not Evaluate as efficiently as current versions.

Dates Time.xlsm
ABCD
1DatesTextDate
2470527ab12efg1947-05-271947-05-2727-May-47
3500113ab12efg1950-01-131950-01-1313-Jan-50
4000630ab12efg2000-06-302000-06-3030-Jun-00
5050114ab12efg2005-01-142005-01-1414-Jan-05
6
4i
Cell Formulas
RangeFormula
D2:D5D2=--(TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00"))


VBA Code: 
[RANGE=rs:6|cs:4|w:Dates Time.xlsm|cls:xl2bb-210|s:4i|tw:286][XR][XH][/XH][XH=w:85]A[/XH][XH=w:85]B[/XH][XH=w:58]C[/XH][XH=w:58]D[/XH][/XR][XR][XH]1[/XH][XD=ch:20.4][/XD][XD=h:l|v:m|cls:ww]Dates[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww]Text[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww]Date[/XD][/XR][XR][XH]2[/XH][XD=h:l|v:m|ch:15|ff:ari|fz:10pt|cls:ww]470527ab12efg[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww|tx:17314|nf:yyyy-mm-dd]1947-05-27[/XD][XD=v:m|ff:ari|fz:10pt|cls:ww|tx:17314|nf:yyyy/mm/dd]1947-05-27[/XD][XD=v:m|ff:ari|fz:10pt|cls:fx ww|nf:d-mmm-yy][FORMULA==--(TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00"))]27-May-47[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:l|v:m|ch:15|ff:ari|fz:10pt|cls:ww]500113ab12efg[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww|tx:18276|nf:yyyy-mm-dd]1950-01-13[/XD][XD=v:m|ff:ari|fz:10pt|cls:ww|tx:18276|nf:yyyy/mm/dd]1950-01-13[/XD][XD=v:m|ff:ari|fz:10pt|cls:fx ww|nf:d-mmm-yy][FORMULA==--(TEXT(20-(LEFT(A3,2)-45>0)&LEFT(A3,6),"0-00-00"))]13-Jan-50[/FORMULA][/XD][/XR][XR][XH]4[/XH][XD=h:l|v:m|ch:15|ff:ari|fz:10pt|cls:ww]000630ab12efg[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww|tx:36707|nf:yyyy-mm-dd]2000-06-30[/XD][XD=v:m|ff:ari|fz:10pt|cls:ww|tx:36707|nf:yyyy/mm/dd]2000-06-30[/XD][XD=v:m|ff:ari|fz:10pt|cls:fx ww|nf:d-mmm-yy][FORMULA==--(TEXT(20-(LEFT(A4,2)-45>0)&LEFT(A4,6),"0-00-00"))]30-Jun-00[/FORMULA][/XD][/XR][XR][XH]5[/XH][XD=h:l|v:m|ch:15|ff:ari|fz:10pt|cls:ww]050114ab12efg[/XD][XD=h:l|v:m|ff:ari|fz:10pt|cls:ww|tx:38366|nf:yyyy-mm-dd]2005-01-14[/XD][XD=v:m|ff:ari|fz:10pt|cls:ww|tx:38366|nf:yyyy/mm/dd]2005-01-14[/XD][XD=v:m|ff:ari|fz:10pt|cls:fx ww|nf:d-mmm-yy][FORMULA==--(TEXT(20-(LEFT(A5,2)-45>0)&LEFT(A5,6),"0-00-00"))]14-Jan-05[/FORMULA][/XD][/XR][XR][XH]6[/XH][XD=ch:15][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE][RANGE=cls:xl2bb-extra-210|t:cf][XR][XD]D2:D5[/XD][XD=fw:b]D2[/XD][XD]=--(TEXT(20-(LEFT(A2,2)-45>0)&LEFT(A2,6),"0-00-00"))[/XD][/XR][/RANGE]
 
Upvote 0
My post of the VBA does not show properly.

I just changed the format row to .NumberFormat = "yyyy-mm-dd"
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Extract date from "number"
Replies
5
Views
359
Peter_SSs
Peter_SSs
muhammad susanto
  • Solved
Convert Format Date From Mm/Dd/Yyyy to Dd/Mm/Yyyy
2
Replies
15
Views
995
nisanthp
N
V
  • Solved
Convert to Date "dd-mm-yyyy"
2
Replies
16
Views
1K
Akuini
Akuini
P
  • Solved
Transpose with dates problem - from the dd/mm/yy to mm/dd/yyyy
Replies
4
Views
407
Alex Blakenburg
A
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
338
KRice
K

Forum statistics

Threads
1,215,097
Messages
6,123,076
Members
449,094
Latest member
mystic19

Share this page

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
Back
Top