R

#### Rick Rothstein \(MVP - VB\)

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

Rick

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

R

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

Rick

G

Gord

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

R

=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)

R

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

R

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

R

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)

R

surprises me is how tolerant the LOOKUP function is for the location of the

array portion of the formula.

Rick

R

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)

H

--

HJN

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