Converting a number todate
 

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

Converting a number todate
 

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" wrote in message
...
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

Converting a number todate
 

=TEXT(A1,"yymmdd")

--
---
HTH

Bob


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



"Hennie Neuhoff" wrote in message
...
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

Converting a number todate
 

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" wrote in message
...
=TEXT(A1,"yymmdd")

--
---
HTH

Bob


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



"Hennie Neuhoff" wrote in
message ...
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

Converting a number todate
 

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" wrote in message
...
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

Converting a number todate
 

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" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

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)" wrote in
message ...
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" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

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" wrote in message
...
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

Converting a number todate
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
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

Converting a number todate
 

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" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

Here is a corrected formula that will do what I intended my first (flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900+100* (DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=TODAY( ))+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR(NOW( )),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
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

Converting a number todate
 

Maybe this:

With A1 formatted as TEXT

B1:
=--(TEXT(MID(A1,3,4),"00\/00\/")&IF(--(TEXT(MID(A1,3,4),"00\/00\/")&
"20"&LEFT(A1,2))TODAY(),19,20)&LEFT(A1,2))

Does that help?
--------------------------

Regards,

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


"Ron Coderre" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

Very nice! Your formula seems to generate the same values mine does, but
yours is shorter and uses a lot less function calls.

Rick


"Ron Coderre" wrote in message
...
Maybe this:

With A1 formatted as TEXT

B1:
=--(TEXT(MID(A1,3,4),"00\/00\/")&IF(--(TEXT(MID(A1,3,4),"00\/00\/")&
"20"&LEFT(A1,2))TODAY(),19,20)&LEFT(A1,2))

Does that help?
--------------------------

Regards,

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


"Ron Coderre" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

On Sun, 10 Feb 2008 19:35:03 -0800, Hennie Neuhoff
wrote:

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

For a formula, perhaps:

=DATE(INT(A1/10^11)+1900+100*(INT(A1/10^11)<YEAR(TODAY())/100),
MOD(INT(A1/10^9),100),MOD(INT(A1/10^7),100))


--ron

Converting a number todate
 

Thanks,Rick....Not being an Excel "date formula guru", these are fun to play
with.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

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

Regards,

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Very nice! Your formula seems to generate the same values mine does, but
yours is shorter and uses a lot less function calls.

Rick


"Ron Coderre" wrote in message
...
Maybe this:

With A1 formatted as TEXT

B1:
=--(TEXT(MID(A1,3,4),"00\/00\/")&IF(--(TEXT(MID(A1,3,4),"00\/00\/")&
"20"&LEFT(A1,2))TODAY(),19,20)&LEFT(A1,2))

Does that help?
--------------------------

Regards,

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


"Ron Coderre" wrote in message
...
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" wrote in
message
...
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

Converting a number todate
 

I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first (flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900+100 *(DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=TODAY ())+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR(NOW ()),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
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

Converting a number todate
 

Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is definitely
a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another formula
that turns out to be 3 characters shorter than your posted (still using 6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't think
that affect the functionality of formula any), then my formula becomes only
1 character shorter.

Rick

Converting a number todate
 

Ron posted that process a little later on in this thread and I responded
with what I thought was an incorrect result from it (given the OP's stated
meaning of the first 6 digits). The example I gave was the first six digits
being 220210 and the fact that Text To Column yielded a date of 2/10/2022
instead of 2/10/1922 which is what I presume the correct result should be.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first (flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900+10 0*(DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=TODA Y())+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR(NO W()),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
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

Converting a number todate
 

Missed that part of the thread.

Change the Regional Settings in Windows if the years will be prior to current
range of 1939 to 2039 or whaterver.

Yeah, I know..........that is a pain in the butt.


Gord

On Tue, 12 Feb 2008 14:55:39 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Ron posted that process a little later on in this thread and I responded
with what I thought was an incorrect result from it (given the OP's stated
meaning of the first 6 digits). The example I gave was the first six digits
being 220210 and the fact that Text To Column yielded a date of 2/10/2022
instead of 2/10/1922 which is what I presume the correct result should be.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first (flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900+1 00*(DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=TOD AY())+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR(N OW()),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
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

Converting a number todate
 

Not to mention making that change would affect every other application that
uses 2-digit dates on the user's system from then on out. Or were you
proposing changing it temporarily? If so, I got the impression that the OP
needed to do this conversion to birthdates more than just one time (he
mentioned the user keying in the code to get the date via a macro).

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Missed that part of the thread.

Change the Regional Settings in Windows if the years will be prior to
current
range of 1939 to 2039 or whaterver.

Yeah, I know..........that is a pain in the butt.


Gord

On Tue, 12 Feb 2008 14:55:39 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Ron posted that process a little later on in this thread and I responded
with what I thought was an incorrect result from it (given the OP's stated
meaning of the first 6 digits). The example I gave was the first six
digits
being 220210 and the fact that Text To Column yielded a date of 2/10/2022
instead of 2/10/1922 which is what I presume the correct result should be.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first
(flawed)
formula to do...

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

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in
message ...
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

Converting a number todate
 

.... would affect every other application that uses 2-digit dates....

2-digit dates??? Uh, I meant 2-digit years in their dates (obviously).

Rick

Converting a number todate
 

The change would be temporary just for the one operation.

Gord

On Tue, 12 Feb 2008 15:27:09 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Not to mention making that change would affect every other application that
uses 2-digit dates on the user's system from then on out. Or were you
proposing changing it temporarily? If so, I got the impression that the OP
needed to do this conversion to birthdates more than just one time (he
mentioned the user keying in the code to get the date via a macro).

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Missed that part of the thread.

Change the Regional Settings in Windows if the years will be prior to
current
range of 1939 to 2039 or whaterver.

Yeah, I know..........that is a pain in the butt.


Gord

On Tue, 12 Feb 2008 14:55:39 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Ron posted that process a little later on in this thread and I responded
with what I thought was an incorrect result from it (given the OP's stated
meaning of the first 6 digits). The example I gave was the first six
digits
being 220210 and the fact that Text To Column yielded a date of 2/10/2022
instead of 2/10/1922 which is what I presume the correct result should be.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I just ran 520210987654 through DataText to ColumnFixed Width.

Select first 6 numbers and Column Data FormatYMD.

Select other column and "Skip" then Finish.

February 10, 1952 was the result in Column A

After formatting of course.


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:17:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a corrected formula that will do what I intended my first
(flawed)
formula to do...

=DATE(IF(LEFT(A1,2)=RIGHT(YEAR(NOW()),2),1900 +100*(DATE(YEAR(NOW()),MID(A1,3,2),MID(A1,5,2))<=T ODAY())+LEFT(A1,2),1900+100*(LEFT(A1,2)<RIGHT(YEAR (NOW()),2))+LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2))

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in
message ...
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

Converting a number todate
 

Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another formula
that turns out to be 3 characters shorter than your posted (still using 6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't think
that affect the functionality of formula any), then my formula becomes
only 1 character shorter.

Rick

Converting a number todate
 

Very nice! Way shorter and 2 function calls less.

By the way, you can save one more character...

=LOOKUP(NOW(),--({19,20}&TEXT(LEFT(A1,6),"00-00-00")))

See what teamwork can accomplish.<g

Rick


"Ron Coderre" wrote in message
...
Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another formula
that turns out to be 3 characters shorter than your posted (still using 6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't think
that affect the functionality of formula any), then my formula becomes
only 1 character shorter.

Rick

Converting a number todate
 

If I am not mistaken, we can save an additional 2 characters (over and above
the 1 character I previously showed being saved) like so...

=LOOKUP(NOW(),--TEXT({19,20}&LEFT(A15,6),"00-00-00"))

My quick tests shows this formula returns the same dates as those longer
formulas.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Very nice! Way shorter and 2 function calls less.

By the way, you can save one more character...

=LOOKUP(NOW(),--({19,20}&TEXT(LEFT(A1,6),"00-00-00")))

See what teamwork can accomplish.<g

Rick


"Ron Coderre" wrote in message
...
Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another
formula
that turns out to be 3 characters shorter than your posted (still using
6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't think
that affect the functionality of formula any), then my formula becomes
only 1 character shorter.

Rick

Converting a number todate
 

Ya know that intuitive thing I mentioned?

I changed the formula to this:
B1: =LOOKUP(NOW(),--TEXT(LEFT(A1,6),{19,20}&"00-00-00"))

Even though that is practically the same,
I think we'd have less explaining to do.
It's slightly more obvious that we're
prepending 19/20 to the number format.

With this:
B1: =LOOKUP(NOW(),--TEXT({19,20}&LEFT(A1,6),"00-00-00"))
I puzzled for a second about how the formatted result would look.
(Yeah...I know...Picky, Picky, Picky)
--------------------------

Regards,

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
If I am not mistaken, we can save an additional 2 characters (over and
above the 1 character I previously showed being saved) like so...

=LOOKUP(NOW(),--TEXT({19,20}&LEFT(A15,6),"00-00-00"))

My quick tests shows this formula returns the same dates as those longer
formulas.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Very nice! Way shorter and 2 function calls less.

By the way, you can save one more character...

=LOOKUP(NOW(),--({19,20}&TEXT(LEFT(A1,6),"00-00-00")))

See what teamwork can accomplish.<g

Rick


"Ron Coderre" wrote in message
...
Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another
formula
that turns out to be 3 characters shorter than your posted (still using
6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't
think
that affect the functionality of formula any), then my formula becomes
only 1 character shorter.

Rick

Converting a number todate
 

Picky? Nah, not really... I can buy your logic for locating it there. What
surprises me is how tolerant the LOOKUP function is for the location of the
array portion of the formula.

Rick


"Ron Coderre" wrote in message
...
Ya know that intuitive thing I mentioned?

I changed the formula to this:
B1: =LOOKUP(NOW(),--TEXT(LEFT(A1,6),{19,20}&"00-00-00"))

Even though that is practically the same,
I think we'd have less explaining to do.
It's slightly more obvious that we're
prepending 19/20 to the number format.

With this:
B1: =LOOKUP(NOW(),--TEXT({19,20}&LEFT(A1,6),"00-00-00"))
I puzzled for a second about how the formatted result would look.
(Yeah...I know...Picky, Picky, Picky)
--------------------------

Regards,

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
If I am not mistaken, we can save an additional 2 characters (over and
above the 1 character I previously showed being saved) like so...

=LOOKUP(NOW(),--TEXT({19,20}&LEFT(A15,6),"00-00-00"))

My quick tests shows this formula returns the same dates as those longer
formulas.

Rick


"Rick Rothstein (MVP - VB)" wrote
in message ...
Very nice! Way shorter and 2 function calls less.

By the way, you can save one more character...

=LOOKUP(NOW(),--({19,20}&TEXT(LEFT(A1,6),"00-00-00")))

See what teamwork can accomplish.<g

Rick


"Ron Coderre" wrote in message
...
Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another
formula
that turns out to be 3 characters shorter than your posted (still
using 6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't
think
that affect the functionality of formula any), then my formula becomes
only 1 character shorter.

Rick

Converting a number todate
 

What surprises me is how tolerant the LOOKUP function is for the location
of the array portion of the formula.

Hence, my initial puzzlement.
It worked....but it just looked like it wouldn't.

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

Best Regards,

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



"Rick Rothstein (MVP - VB)" wrote in
message ...
Picky? Nah, not really... I can buy your logic for locating it there. What
surprises me is how tolerant the LOOKUP function is for the location of
the array portion of the formula.

Rick


"Ron Coderre" wrote in message
...
Ya know that intuitive thing I mentioned?

I changed the formula to this:
B1: =LOOKUP(NOW(),--TEXT(LEFT(A1,6),{19,20}&"00-00-00"))

Even though that is practically the same,
I think we'd have less explaining to do.
It's slightly more obvious that we're
prepending 19/20 to the number format.

With this:
B1: =LOOKUP(NOW(),--TEXT({19,20}&LEFT(A1,6),"00-00-00"))
I puzzled for a second about how the formatted result would look.
(Yeah...I know...Picky, Picky, Picky)
--------------------------

Regards,

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


"Rick Rothstein (MVP - VB)" wrote
in message ...
If I am not mistaken, we can save an additional 2 characters (over and
above the 1 character I previously showed being saved) like so...

=LOOKUP(NOW(),--TEXT({19,20}&LEFT(A15,6),"00-00-00"))

My quick tests shows this formula returns the same dates as those longer
formulas.

Rick


"Rick Rothstein (MVP - VB)" wrote
in message ...
Very nice! Way shorter and 2 function calls less.

By the way, you can save one more character...

=LOOKUP(NOW(),--({19,20}&TEXT(LEFT(A1,6),"00-00-00")))

See what teamwork can accomplish.<g

Rick


"Ron Coderre" wrote in message
...
Hey....That gives me an idea!

=LOOKUP(NOW(),--(20-{1,0}&TEXT(LEFT(A1,6),"00-00-00")))

Sometimes the shortest formula is so arcane as
to "technically" work, but make no intuitive sense.
Hopefully, that one doesn't cross that line.

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


"Rick Rothstein (MVP - VB)"
wrote in
message ...
Thanks,Rick....Not being an Excel "date formula guru", these are fun
to play with.

Yes, struggling to find shorter and shorter formula solutions is
definitely a fun process.

I worked on it a little more and came up with this:
=LOOKUP(TODAY(),INDEX(--(TEXT(MID(A1,3,4),"00\/00\/")&{"19","20"}&LEFT(A1,2)),0))

A nice, thinking-outside-of-the-box solution. Here is yet another
formula
that turns out to be 3 characters shorter than your posted (still
using 6
function calls though)...

=DATEVALUE((20-(LEFT(A1,6)TEXT(NOW(),"yymmdd")))&TEXT(LEFT(A1,6) ,"00-00-00"))

However, if you replace your TODAY function call with NOW (I don't
think
that affect the functionality of formula any), then my formula
becomes
only 1 character shorter.

Rick

Converting a number todate
 

Thank you so much. I enjoyed the discussion between your guys!!
--
HJN


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in
message
...
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