엑셀 생년월일 6자리 앞자리 0 사라짐? TEXT·DATE로 날짜 변환하고 나이 계산하기
엑셀에서 생년월일 6자리나 주민번호 앞 6자리를 입력했는데 010203이 10203으로 바뀌는 문제, 초보자분들이 정말 자주 만납니다. 겉으로는 숫자 하나 빠진 것처럼 보이지만, 이 상태에서 LEFT, MID, DATE 함수를 쓰면 생년월일 변환도 틀어지고 나이 계산도 이상해집니다.
오늘은 실제 명단 파일에서 바로 만나는 상황으로 설명해 보겠습니다. 기존에는 앞자리 0을 눈으로 확인하고 직접 고쳤다면, 개선 후에는 TEXT 함수로 6자리를 맞춘 뒤 DATE 함수로 진짜 날짜를 만드는 방식으로 정리합니다.
Before / After로 보면 차이가 바로 보입니다
아래처럼 고객 명단이나 교육 신청자 명단을 받았다고 가정해 보겠습니다. A열에는 이름, B열에는 생년월일 6자리가 들어 있습니다. 문제는 B열에 010203처럼 0으로 시작하는 값이 있으면 엑셀이 숫자로 판단해서 10203으로 바꿔 버린다는 점입니다.
| 구분 | 기존 방식 | 개선 후 방식 |
|---|---|---|
| 앞자리 0 처리 | 눈으로 보고 직접 0 추가 | TEXT 함수로 항상 6자리 표시 |
| 날짜 변환 | 셀 서식만 날짜로 바꿈 | DATE 함수로 실제 날짜 생성 |
| 나이 계산 | 연도만 빼서 대략 계산 | 생일 지났는지까지 IF로 확인 |
| 실무 안정성 | 행이 많아지면 실수 증가 | 수식 복사로 한 번에 처리 |
예제 파일 구조부터 정확히 잡고 시작하기
예제는 3행에 제목이 있고, 실제 데이터는 4행부터 9행까지 있다고 보겠습니다. 결과는 C열, D열, E열에 표시합니다. 기준일은 H2 셀에 입력해 둡니다.
| 셀 범위 | 열 이름 | 설명 |
|---|---|---|
| A3:A9 | 이름 | 명단 대상자 이름 |
| B3:B9 | 생년월일6자리 | 예: 990305, 10203, 851231 |
| C3:C9 | 변환 생년월일 | DATE 함수로 만든 실제 날짜 |
| D3:D9 | 만 나이 | H2 기준일로 계산 |
| E3:E9 | 구분 | 성인 / 미성년 표시 |
| H2 | 기준일 | 예: 2026-07-13 |
| H5 | 성인 인원 | COUNTIF 결과 표시 |
실제 입력 예시는 아래와 같습니다. 여기서 이도윤의 생년월일은 원래 010203이어야 하는데, 엑셀에서 숫자로 들어와 10203처럼 보이는 상황입니다.
| A열 이름 | B열 생년월일6자리 | 비고 |
|---|---|---|
| 김가람 | 990305 | 1999년생 |
| 이도윤 | 10203 | 원래 010203 |
| 박서진 | 851231 | 1985년생 |
| 최민준 | 70715 | 원래 070715 |
| 정하린 | 300101 | 1930년생으로 처리 |
| 한유나 | 229 | 원래 000229 |
왜 그냥 LEFT, MID를 쓰면 헷갈릴까요?
LEFT 함수는 왼쪽에서 글자를 가져오는 함수입니다. 예를 들어 =LEFT(B4,2)라고 쓰면 B4의 왼쪽 두 글자를 가져옵니다. 그런데 B5 셀 값이 010203이 아니라 10203으로 저장되어 있으면 왼쪽 두 글자는 01이 아니라 10이 됩니다.
그래서 생년월일 6자리처럼 자리수가 중요한 데이터는 먼저 6자리 모양을 맞춰야 합니다. 이때 쓰는 함수가 TEXT입니다. TEXT는 숫자를 원하는 표시 형식의 글자로 바꿔 줍니다.
=TEXT(B4,"000000")이 수식은 B4 값이 10203이어도 010203처럼 6자리로 맞춰 줍니다. 앞의 0은 계산용 숫자라기보다 코드의 일부라고 보면 됩니다. 상품코드, 사번, 거래처코드, 생년월일 6자리처럼 앞자리 0이 의미 있는 데이터는 엑셀에서 자주 헷갈리는 대표 사례입니다.
C4 셀에 생년월일을 날짜로 변환하는 수식 입력
이제 C4 셀에 변환 생년월일을 만들겠습니다. C4 셀을 클릭한 뒤 아래 수식을 입력하고, C9까지 아래로 복사합니다.
아래 수식은 생년월일 6자리의 앞 두 자리를 연도로 보고, 30 이상이면 1900년대, 30 미만이면 2000년대로 처리합니다. 예제에서는 300101을 1930-01-01로 보겠다는 뜻입니다. 회사 파일의 대상자가 모두 2000년대생이라면 이 기준은 바꿔야 합니다.
=DATE(IF(LEFT(TEXT(B4,"000000"),2)*1>=30,1900+LEFT(TEXT(B4,"000000"),2)*1,2000+LEFT(TEXT(B4,"000000"),2)*1),MID(TEXT(B4,"000000"),3,2)*1,RIGHT(TEXT(B4,"000000"),2)*1)수식이 길어 보이지만 뜯어보면 어렵지 않습니다. TEXT(B4,"000000")은 6자리로 맞추는 부분입니다. LEFT는 연도, MID는 월, RIGHT는 일을 가져옵니다.
| 수식 조각 | 뜻 | 예: 010203 |
|---|---|---|
| TEXT(B4,"000000") | 무조건 6자리로 맞춤 | 010203 |
| LEFT(...,2) | 앞 2자리, 연도 | 01 |
| MID(...,3,2) | 3번째부터 2자리, 월 | 02 |
| RIGHT(...,2) | 오른쪽 2자리, 일 | 03 |
| DATE(연,월,일) | 진짜 날짜로 조립 | 2001-02-03 |
여기서 중요한 점은 셀 서식만 날짜로 바꾸는 것이 아니라는 점입니다. 셀 서식은 보이는 모양만 바꾸는 기능이고, DATE 함수는 엑셀이 계산할 수 있는 실제 날짜 값을 만들어 줍니다. 나이 계산이나 월별 집계를 하려면 실제 날짜로 만들어 두는 편이 안전합니다.
D4 셀에서 기준일 기준 만 나이 계산하기
이번에는 D4 셀에 만 나이를 계산하겠습니다. 기준일은 H2 셀에 입력해 둡니다. 예를 들어 H2에 2026-07-13을 입력합니다.
D4 셀을 클릭하고 아래 수식을 입력한 뒤 D9까지 복사합니다.
=YEAR($H$2)-YEAR(C4)-IF(TEXT($H$2,"mmdd")<TEXT(C4,"mmdd"),1,0)이 수식은 먼저 기준일의 연도에서 생년월일의 연도를 뺍니다. 그다음 올해 생일이 아직 지나지 않았다면 1살을 빼 줍니다. 그래서 단순히 2026-2001처럼 연도만 빼는 것보다 실무에서 더 정확합니다.
여기서 $H$2처럼 달러 표시가 붙어 있는 이유도 꼭 알아두면 좋습니다. D4 수식을 아래로 복사해도 기준일 셀은 계속 H2를 보게 하려는 고정 표시입니다. 이걸 빼고 복사하면 D5에서는 H3, D6에서는 H4를 보게 되어 결과가 틀어질 수 있습니다.
E4 셀에서 성인 / 미성년 자동 표시
나이를 계산했으면 이제 구분을 자동으로 넣을 수 있습니다. E4 셀에 아래 수식을 입력하고 E9까지 복사합니다.
=IF(D4>=19,"성인","미성년")IF 함수는 조건에 따라 다른 값을 보여주는 함수입니다. 위 수식은 D4의 만 나이가 19 이상이면 성인, 아니면 미성년이라고 표시합니다. 회사 기준이나 업무 기준에 따라 18세, 20세 등으로 다르게 봐야 한다면 19 숫자만 바꾸면 됩니다.
성인 인원까지 COUNTIF로 바로 세기
보고서에는 보통 개인별 결과만 필요한 것이 아니라 총 몇 명인지도 필요합니다. H5 셀에 성인 인원을 표시한다고 해 보겠습니다. H5 셀을 클릭하고 아래 수식을 입력합니다.
=COUNTIF(E4:E9,"성인")COUNTIF는 조건에 맞는 셀 개수를 세는 함수입니다. 여기서는 E4:E9 범위에서 값이 성인인 셀만 세겠다는 뜻입니다. 데이터가 500행까지 있다면 범위를 E4:E500처럼 바꿔 주면 됩니다.
| 결과 셀 | 입력 수식 | 역할 |
|---|---|---|
| C4 | DATE + LEFT/MID/RIGHT | 6자리 값을 날짜로 변환 |
| D4 | YEAR + IF + TEXT | 기준일 기준 만 나이 계산 |
| E4 | IF | 성인/미성년 구분 |
| H5 | COUNTIF | 성인 인원 집계 |
초보자가 여기서 자주 틀리는 부분
첫 번째는 B열을 날짜 서식으로 바꾸는 실수입니다. 990305는 날짜가 아니라 생년월일을 줄여 쓴 코드에 가깝습니다. 이 상태에서 셀 서식만 날짜로 바꾸면 엑셀이 전혀 다른 날짜로 해석할 수 있습니다.
두 번째는 앞자리 0이 빠진 값을 그대로 LEFT 함수에 넣는 실수입니다. 070715가 70715로 저장되어 있으면 왼쪽 두 자리는 07이 아니라 70이 됩니다. 그래서 반드시 TEXT(B4,"000000")으로 자리수를 맞춘 뒤 글자를 잘라야 합니다.
세 번째는 1900년대와 2000년대 기준을 정하지 않는 실수입니다. 990305는 대부분 1999년생으로 보면 되지만, 010203은 1901년인지 2001년인지 파일만 봐서는 알 수 없습니다. 실무에서는 대상자 범위를 보고 기준을 정해야 합니다.
네 번째는 H2 기준일을 고정하지 않고 수식을 복사하는 실수입니다. 나이 계산 수식에서 $H$2를 쓰지 않으면 아래로 복사할 때 기준일 참조가 밀립니다. 결과가 한두 행만 이상하다면 이 부분부터 확인해 보세요.
결과가 이상할 때 확인 순서
먼저 B열 값이 정말 생년월일 6자리인지 확인합니다. 중간에 하이픈이 들어간 값, 공백이 붙은 값, 7자리 이상 값이 섞여 있으면 수식 결과가 달라질 수 있습니다. 이 글의 수식은 B열에 숫자 형태의 6자리 생년월일이 들어오는 상황을 기준으로 합니다.
다음으로 C열 결과가 날짜처럼 보이는지 확인합니다. C4 셀에 수식을 넣었는데 36895 같은 숫자가 보인다면 날짜 값은 만들어졌지만 표시 형식이 일반으로 되어 있을 수 있습니다. 이때는 C열을 선택하고 홈 탭에서 표시 형식을 날짜로 바꾸면 됩니다.
마지막으로 30년 기준이 내 파일과 맞는지 확인합니다. 예제 수식은 앞 두 자리가 30 이상이면 1900년대, 29 이하이면 2000년대로 봅니다. 만약 명단에 2030년생 유아가 포함될 수 있는 파일이라면 이 기준은 적절하지 않습니다.
내 파일에 맞게 바꿔 쓰는 응용 팁
데이터가 4행이 아니라 2행부터 시작한다면 수식의 B4, C4, D4를 각각 B2, C2, D2로 바꿔 쓰면 됩니다. 중요한 것은 첫 데이터 행에 맞춰 수식을 넣고 아래로 복사하는 것입니다.
성인 기준을 다른 셀에 따로 입력해 두고 싶다면, 예를 들어 H3 셀에 기준 나이 19를 입력해 둘 수 있습니다. 그러면 E4 수식은 아래처럼 바꿀 수 있습니다.
=IF(D4>=$H$3,"성인","미성년")이렇게 해 두면 성인 기준이 바뀔 때 수식을 다시 고치지 않아도 됩니다. H3 숫자만 18이나 20으로 바꾸면 전체 결과가 다시 계산됩니다.
명단이 계속 늘어나는 파일이라면 C4:E4 수식을 충분히 아래 행까지 미리 복사해 두는 것도 방법입니다. 다만 빈 행까지 수식이 보이는 것이 싫다면 데이터가 들어온 뒤 필요한 만큼만 복사하는 편이 초보자에게는 더 관리하기 쉽습니다.
이 방식으로 정리하면 좋은 업무
교육 신청자 명단에서 나이대별 대상자를 구분할 때, 이벤트 참여자 중 미성년 여부를 확인할 때, 회원 명단에서 생년월일을 날짜로 바꿔 월별 생일자를 뽑을 때 모두 같은 방식으로 응용할 수 있습니다.
핵심은 간단합니다. 생년월일 6자리는 먼저 TEXT로 6자리 복원, 그다음 LEFT/MID/RIGHT로 연월일 분리, 마지막으로 DATE로 실제 날짜 만들기입니다. 이 흐름만 익혀 두면 앞자리 0 때문에 생기는 엑셀 오류를 훨씬 덜 겪게 됩니다.