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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

O
  • Solved
Convert timestamp format to DD/MM/YYY
2 3 4
Replies
35
Views
433
Joe4
Joe4
muhammad susanto
  • Solved
Extract Data to be Date Format (dd-mm-yyyy)
2
Replies
16
Views
283
muhammad susanto
muhammad susanto
T
  • Solved
Extract date from "number"
Replies
5
Views
609
Peter_SSs
Peter_SSs
muhammad susanto
  • Solved
Convert Format Date From Mm/Dd/Yyyy to Dd/Mm/Yyyy
2
Replies
15
Views
2K
nisanthp
N
A
  • Solved
Date format changing ...
Replies
4
Views
220
adambc
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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