In our country all citizens have a unique [13digit] identity number, the
first 6 digits being date of bitrh. I've tried to convert these 6 digits to
date of birth, without
any luck. The main problem being the year of birth forms the first 2 digits,
ie a person born on 10 February 1952 IDnumber would be 520210[plus 7 digits] .
Any help would be appreciated
HJN

I am guessing no one in your country will ever live to be more than 100?
Here is a formula that seems to work for those less than 100 years old...

=DATE(1900+100*(IF(LEFT(A1,2)<=RIGHT(YEAR(NOW()),2),IF(--MID(A1,3,2)<=MONTH(NOW()),IF(--MID(A1,5,2)<=DAY(NOW()),1,0),0),0))+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

Rick


"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
> In our country all citizens have a unique [13digit] identity number, the
> first 6 digits being date of bitrh. I've tried to convert these 6 digits
> to
> date of birth, without
> any luck. The main problem being the year of birth forms the first 2
> digits,
> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
> digits] .
> Any help would be appreciated
> HJN

=TEXT(A1,"yymmdd")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
> In our country all citizens have a unique [13digit] identity number, the
> first 6 digits being date of bitrh. I've tried to convert these 6 digits
> to
> date of birth, without
> any luck. The main problem being the year of birth forms the first 2
> digits,
> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
> digits] .
> Any help would be appreciated
> HJN

You are going in the wrong direction... the OP has the number and wants to
produce a date from it... your formula assumes just the opposite (that the
OP has the date and wants to produce the number).

Rick


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =TEXT(A1,"yymmdd")
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "Hennie Neuhoff" <(E-Mail Removed)> wrote in
> message news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
>> In our country all citizens have a unique [13digit] identity number, the
>> first 6 digits being date of bitrh. I've tried to convert these 6 digits
>> to
>> date of birth, without
>> any luck. The main problem being the year of birth forms the first 2
>> digits,
>> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
>> digits] .
>> Any help would be appreciated
>> HJN
>
>

Try this to create a list of the dates off to the right of the original
values:

Select the single column range of ID Numbers

From the Excel Main Menu:
<data><text-to-columns>
....Check: Fixed Width.......Click [Next]
....Insert a breakpoint after the 6th character (by clicking)...Click [Next]
....Select the 1st Col...Check: Date...YMD...Click [Next]
....Select the 2st Col...Check: Do not import
....Destinatioin: Select a cell off to the right of the 1st ID Number
....Click [Finish]


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
> In our country all citizens have a unique [13digit] identity number, the
> first 6 digits being date of bitrh. I've tried to convert these 6 digits
> to
> date of birth, without
> any luck. The main problem being the year of birth forms the first 2
> digits,
> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
> digits] .
> Any help would be appreciated
> HJN

I'm not sure where the 1900/2000 century switchover breakpoint is, but if
the cell contains this number...

2202103242344

as an example (the numbers from position 7 onward are immaterial), using
your Text To Column procedure will convert it to this...

2/10/2022

Based on what the OP said the first 6-digits represented, I would guess the
year should be 1922, not 2022.

Rick


"Ron Coderre" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> Try this to create a list of the dates off to the right of the original
> values:
>
> Select the single column range of ID Numbers
>
> From the Excel Main Menu:
> <data><text-to-columns>
> ...Check: Fixed Width.......Click [Next]
> ...Insert a breakpoint after the 6th character (by clicking)...Click
> [Next]
> ...Select the 1st Col...Check: Date...YMD...Click [Next]
> ...Select the 2st Col...Check: Do not import
> ...Destinatioin: Select a cell off to the right of the 1st ID Number
> ...Click [Finish]
>
>
> Is that something you can work with?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Hennie Neuhoff" <(E-Mail Removed)> wrote in
> message
> news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
>> In our country all citizens have a unique [13digit] identity number, the
>> first 6 digits being date of bitrh. I've tried to convert these 6 digits
>> to
>> date of birth, without
>> any luck. The main problem being the year of birth forms the first 2
>> digits,
>> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
>> digits] .
>> Any help would be appreciated
>> HJN
>
>
>

Not sure if that's an issue or not.
We'll see, I guess.

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> I'm not sure where the 1900/2000 century switchover breakpoint is, but if
> the cell contains this number...
>
> 2202103242344
>
> as an example (the numbers from position 7 onward are immaterial), using
> your Text To Column procedure will convert it to this...
>
> 2/10/2022
>
> Based on what the OP said the first 6-digits represented, I would guess
> the year should be 1922, not 2022.
>
> Rick
>
>
> "Ron Coderre" <(E-Mail Removed)> wrote in message
> news:O$(E-Mail Removed)...
>> Try this to create a list of the dates off to the right of the original
>> values:
>>
>> Select the single column range of ID Numbers
>>
>> From the Excel Main Menu:
>> <data><text-to-columns>
>> ...Check: Fixed Width.......Click [Next]
>> ...Insert a breakpoint after the 6th character (by clicking)...Click
>> [Next]
>> ...Select the 1st Col...Check: Date...YMD...Click [Next]
>> ...Select the 2st Col...Check: Do not import
>> ...Destinatioin: Select a cell off to the right of the 1st ID Number
>> ...Click [Finish]
>>
>>
>> Is that something you can work with?
>> Post back if you have more questions.
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>> "Hennie Neuhoff" <(E-Mail Removed)> wrote in
>> message
>> news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
>>> In our country all citizens have a unique [13digit] identity number, the
>>> first 6 digits being date of bitrh. I've tried to convert these 6 digits
>>> to
>>> date of birth, without
>>> any luck. The main problem being the year of birth forms the first 2
>>> digits,
>>> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
>>> digits] .
>>> Any help would be appreciated
>>> HJN
>>
>>
>>
>

Tanks. I've tried to do this with a macro. The objective being if the user
key in the ID no. to obtain the date of birth.
--
HJN


"Ron Coderre" wrote:

> Try this to create a list of the dates off to the right of the original
> values:
>
> Select the single column range of ID Numbers
>
> From the Excel Main Menu:
> <data><text-to-columns>
> ....Check: Fixed Width.......Click [Next]
> ....Insert a breakpoint after the 6th character (by clicking)...Click [Next]
> ....Select the 1st Col...Check: Date...YMD...Click [Next]
> ....Select the 2st Col...Check: Do not import
> ....Destinatioin: Select a cell off to the right of the 1st ID Number
> ....Click [Finish]
>
>
> Is that something you can work with?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Hennie Neuhoff" <(E-Mail Removed)> wrote in message
> news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
> > In our country all citizens have a unique [13digit] identity number, the
> > first 6 digits being date of bitrh. I've tried to convert these 6 digits
> > to
> > date of birth, without
> > any luck. The main problem being the year of birth forms the first 2
> > digits,
> > ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
> > digits] .
> > Any help would be appreciated
> > HJN
>
>
>
>

In looking at this formula, I am pretty sure it does NOT work correctly, so
don't use it.

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>I am guessing no one in your country will ever live to be more than 100?
>Here is a formula that seems to work for those less than 100 years old...
>
> =DATE(1900+100*(IF(LEFT(A1,2)<=RIGHT(YEAR(NOW()),2),IF(--MID(A1,3,2)<=MONTH(NOW()),IF(--MID(A1,5,2)<=DAY(NOW()),1,0),0),0))+LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))
>
> Rick
>
>
> "Hennie Neuhoff" <(E-Mail Removed)> wrote in
> message news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
>> In our country all citizens have a unique [13digit] identity number, the
>> first 6 digits being date of bitrh. I've tried to convert these 6 digits
>> to
>> date of birth, without
>> any luck. The main problem being the year of birth forms the first 2
>> digits,
>> ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
>> digits] .
>> Any help would be appreciated
>> HJN
>

You should be able to use this function within your macro...

Function GetBirthday(IDnumber As String) As Date
Dim Yr As Long
Dim Mn As Long
Dim Dy As Long
Yr = Left(IDnumber, 2)
Mn = Mid(IDnumber, 3, 2)
Dy = Mid(IDnumber, 5, 2)
If Yr = Year(Now) Mod 2000 Then
Yr = 1900 - 100 * (DateSerial(Year(Now), Mn, Dy) <= Date) + Yr
Else
Yr = 1900 - 100 * (Yr < Year(Now) Mod 2000) + Yr
End If
GetBirthday = DateSerial(Yr, Mn, Dy)
End Function

You could use it something like this...

Sub TestMacro()
Dim Answer As String
Answer = InputBox("Enter ID number...")
If Answer Like "######*" Then
MsgBox "Birthday: " & GetBirthday(Answer)
End If
End Sub

Rick


"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:8A31ABB3-62A0-4E04-9CC3-(E-Mail Removed)...
> Tanks. I've tried to do this with a macro. The objective being if the user
> key in the ID no. to obtain the date of birth.
> --
> HJN
>
>
> "Ron Coderre" wrote:
>
>> Try this to create a list of the dates off to the right of the original
>> values:
>>
>> Select the single column range of ID Numbers
>>
>> From the Excel Main Menu:
>> <data><text-to-columns>
>> ....Check: Fixed Width.......Click [Next]
>> ....Insert a breakpoint after the 6th character (by clicking)...Click
>> [Next]
>> ....Select the 1st Col...Check: Date...YMD...Click [Next]
>> ....Select the 2st Col...Check: Do not import
>> ....Destinatioin: Select a cell off to the right of the 1st ID Number
>> ....Click [Finish]
>>
>>
>> Is that something you can work with?
>> Post back if you have more questions.
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>> "Hennie Neuhoff" <(E-Mail Removed)> wrote in
>> message
>> news:698E7A2E-35A2-4F2B-BE9D-(E-Mail Removed)...
>> > In our country all citizens have a unique [13digit] identity number,
>> > the
>> > first 6 digits being date of bitrh. I've tried to convert these 6
>> > digits
>> > to
>> > date of birth, without
>> > any luck. The main problem being the year of birth forms the first 2
>> > digits,
>> > ie a person born on 10 February 1952 IDnumber would be 520210[plus 7
>> > digits] .
>> > Any help would be appreciated
>> > HJN
>>
>>
>>
>>