Convert 2-digit year

  • Thread starter Thread starter Hennie Neuhoff
  • Start date Start date
H

Hennie Neuhoff

Hi. All,
Easy one for the experts.
My country use a unique [13 number-digit] Identification system.
The first 6-digits represent the date of birth [yymmdd]
In my code the ID number is entered on a userform as text and
displayed as follows 52-02-08-5034-087 [in this example the d.of b
is 8-Feb-1952]
Range("Bithday").Value = Left(Idnumber, 8)
displays the required birthday in Range("Bithday")
With the note "This cell contains a date string represented with
only 2 digits for the year."
If I select the option "Convert XX to 19XX" it gives me exactly
what I want.
I tried macro record but it doesn’t give me any result.
How do I include the "Convert XX to 19XX" in my code ?
 
You added two more character to the string when you chaged the year from two
digits to 4 digits

from
Range("Bithday").Value = Left(Idnumber, 8)
to
Range("Bithday").Value = Left(Idnumber, 10)

You will either get
52-02-08-5034-087 : 17 characters
or
52-02-2008-5034-087 : 19 characters


Os the code would look like this

if len(IdNumber) = 17 then
Range("Bithday").Value = Left(Idnumber, 8)
else
Range("Bithday").Value = Left(Idnumber, 10)
end if
 
You can let VB handle putting in the century for you using this code line...

Range("Bithday").Value = CDate(Mid(IDnumber, 7, 3) & MonthName(Mid( _
IDnumber, 4, 2)) & "-" & Left(IDnumber, 2))

HOWEVER, the point at which VB switches from the 1900s to the 2000s is
determined by the Windows' Regional Settings (which can be changed by the
user). The default breakpoint is 1930... a 2-digit year before 30 will be in
the 2000s (for example, 29 would become 2029 whereas a 2-digit year of 30 or
later would be placed in the 1900 (for example, 30 would become 1930).
 
Rick - Thank you very much - I think Joel did not read to question
--
HJN


Rick Rothstein said:
You can let VB handle putting in the century for you using this code line...

Range("Bithday").Value = CDate(Mid(IDnumber, 7, 3) & MonthName(Mid( _
IDnumber, 4, 2)) & "-" & Left(IDnumber, 2))

HOWEVER, the point at which VB switches from the 1900s to the 2000s is
determined by the Windows' Regional Settings (which can be changed by the
user). The default breakpoint is 1930... a 2-digit year before 30 will be in
the 2000s (for example, 29 would become 2029 whereas a 2-digit year of 30 or
later would be placed in the 1900 (for example, 30 would become 1930).

--
Rick (MVP - Excel)


Hennie Neuhoff said:
Hi. All,
Easy one for the experts.
My country use a unique [13 number-digit] Identification system.
The first 6-digits represent the date of birth [yymmdd]
In my code the ID number is entered on a userform as text and
displayed as follows 52-02-08-5034-087 [in this example the d.of b
is 8-Feb-1952]
Range("Bithday").Value = Left(Idnumber, 8)
displays the required birthday in Range("Bithday")
With the note "This cell contains a date string represented with
only 2 digits for the year."
If I select the option "Convert XX to 19XX" it gives me exactly
what I want.
I tried macro record but it doesn’t give me any result.
How do I include the "Convert XX to 19XX" in my code ?
Click to expand...
Click to expand...
 
Back
Top