Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert 2-digit year
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 doesnt give me any result. How do I include the "Convert XX to 19XX" in my code ? -- HJN |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert 2-digit year
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 "Hennie Neuhoff" wrote: 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 doesnt give me any result. How do I include the "Convert XX to 19XX" in my code ? -- HJN |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert 2-digit year
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" wrote in message ... 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 doesnt give me any result. How do I include the "Convert XX to 19XX" in my code ? -- HJN |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert 2-digit year
Rick - Thank you very much - I think Joel did not read to question
-- HJN "Rick Rothstein" wrote: 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" wrote in message ... 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 doesnt give me any result. How do I include the "Convert XX to 19XX" in my code ? -- HJN |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically convert Text with 2-digit year | Excel Discussion (Misc queries) | |||
convert day of year to numeric value format year+day in 4 digits | Excel Worksheet Functions | |||
Addendum to 2 digit year to 4 digit year | Excel Discussion (Misc queries) | |||
Format 2 digit year to 4 digit | Excel Discussion (Misc queries) | |||
Convert 2 digit month to 4 digit years and months | Excel Worksheet Functions |