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 said: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
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 Data>Text to Column>Fixed Width.
Select first 6 numbers and Column Data Format>YMD.
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\)"
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
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
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
What surprises me is how tolerant the LOOKUP function is for the location
of the array portion of the formula.
Rick Rothstein (MVP - VB) said: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 said: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 said: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)
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
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.