Home > 엑셀 함수 > 날짜 및 시간 함수 > 주민번호에서 생년월일 추출(외국인포함)

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

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

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

29년생, 외국인 포함된 주민등록번호에서 생년월일 추출

 

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

실무에서 다음과 같이 '텍스트 나누기 기능'으로 생년월일을 추출하는 경우가 있는데 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"))

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

 

 

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

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

※ 주민등록번호 7번째 자리 규칙
1 또는 5(외국인) : 1900년대생 남자
2 또는 6(외국인) : 1900년대생 여자
3 또는 7(외국인) : 2000년대생 남자
4 또는 8(외국인) : 2000년대생 여자

※ 참고 : 외국인은 주민등록번호가 아니라 외국인등록번호를 발급받는데, 번호 체계는 주민등록번호 발급체계를 따릅니다. 요즘은 직장, 학교 등에 외국인이 많아 인사시스템 등에 등록할 때 주민등록번호 필드를 같이 사용하는 경우가 많습니다.

주민번호 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함수로 날짜 변환

 

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

주민번호 7번째 자리 규칙으로 생년월일 추출 - 중간 작업열 사용

연 추출 : =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))
참조하는 값만 다를 뿐 위의 수식과 작동 방식은 동일합니다.

 

 


관련 글

DATEVALUE 함수 사용법 DATEVALUE 함수 - 텍스트를 날짜 일련번호로 변환 - 엑셀의 DATEVALUE 함수는 텍스트(문자열)로 표시된 날짜를 '날짜 일련번호'로 바꿔줍니다. 인터넷이나 다른 자료에서 엑셀로 날짜값을 복사해올 때 날짜에 공백이 포함되거나 날짜가…

DATEVALUE 함수 - 텍스트를 날짜 일련번호로 변환 더 보기 »

DATE 함수 - 연월일 값으로 날짜 구하기 - 엑셀 DATE 함수는 연월일에 해당하는 값을 입력받아 날짜 값을 반환해 줍니다. 함수 자체만 보면 별 쓸모없어 보이지만, 자료에서 연월일이 각각의…

DATE 함수 - 연월일 값으로 날짜 구하기 더 보기 »

 

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: