주민번호에서 생년월일 추출(외국인포함)

다음의 주민등록번호를 보면 1900년대 출생도 있고, 2000년 이후 출생도 있고 외국인도 포함되어 있습니다.

주민등록번호 앞 6자리와 7번째 자리를 이용하여 생년월일을 추출해 보겠습니다.

 

텍스트 나누기로 생년월일 추출

실무에서 다음과 같이 '텍스트 나누기 기능'으로 생년월일을 추출하는 경우가 있는데 1929년 이전 출생자가 포함되어 있으면 날짜가 제대로 변환되지 않습니다.

 

엑셀에서 날짜의 연도를 2자리로 입력하면 2029 룰에 의해 00부터 29는 2000년대로 변환하고 30부터 99까지는 1900년대로 변환합니다. 따라서 위의 첫번째 주민번호의 '290928'은 2029-09-28로 잘못 변환됩니다. 주민번호의 7번째 자리가 1이므로 1929-09-28이 맞습니다.

※ 2029룰 참고 : https://docs.microsoft.com/en-us/office/troubleshoot/excel/two-digit-year-numbers

 

 

DATEVALUE, TEXT 함수로 생년월일 추출

다음과 같이 DATEVALUE 함수와 TEXT 함수로 생년월일을 추출하는 경우도 있는데 '텍스트 나누기'와 마찬가지로 날짜가 잘못 변환됩니다.

=DATEVALUE(TEXT(LEFT(B14,6),"00-00-00"))

 

 

주민번호 7번째 자리 규칙으로 생년월일 추출

위에서 살펴본대로 주민등록번호 앞 2자리 생년을 그대로 잘라서 쓰면 29년생이면 2029년으로 잘못 변환되는 문제가 있으므로 아래의 주민등록번호 7번째 자리의 규칙을 이용하여 생년월일을 추출해 보겠습니다.

[C23] 셀에 다음 수식을 입력하고, 수식을 복사하여 나머지 셀 범위 [C24:C27] 범위에 붙여 넣습니다.

=IF(OR(MID(B23,8,1)="3",MID(B23,8,1)="4",MID(B23,8,1)>="7"),DATE("20"&MID(B23,1,2),MID(B23,3,2),MID(B23,5,2)),DATE("19"&MID(B23,1,2),MID(B23,3,2),MID(B23,5,2)))

수식 풀이 :

IF(OR(MID(B23,8,1)="3",MID(B23,8,1)="4",MID(B23,8,1)>="7")
MID 함수로 주민등록번호 열에서 8번째 부터 1자리를 잘라낸 값이 3 또는 4, 또는 7보다 큰가?(2000년대 생인가?)

DATE("20"&MID(B23,1,2),MID(B23,3,2),MID(B23,5,2))
2000년대 생이면 잘라낸 생년월일 앞에 '20'을 붙여서 DATE함수로 날짜 변환

DATE("19"&MID(B23,1,2),MID(B23,3,2),MID(B23,5,2))
2000년대 생이 아니면 잘라낸 생년월일 앞에 '19'를 붙여서 DATE함수로 날짜 변환

 

다음과 같이 중간 작업 열을 추가하여 변환할 수도 있습니다. 한 번에 생년월일을 추출할 수는 없지만 중간 열이 필요한 경우에 사용하면 편리합니다.

연 추출 : =MID(B36,1,2)

월 추출 : =MID(B36,3,2)

일 추출 : =MID(B36,5,2)

7번째자리 추출 : =--MID(B36,8,1)
잘라낸 값이 텍스트이므로 마이너스를 두 번입력(두번 곱하기와 동일 )하여 숫자로 변환

생년월일 변환 : =IF(OR(F36=3,F36=4,F36>=7),DATE("20"&C36,D36,E36),DATE("19"&C36,D36,E36))
참조하는 값만 다를 뿐 위의 수식과 작동 방식은 동일합니다.

 

 

---

관련 글