직원들의 출근시간과 퇴근시간을 매일 기록하다 보면 실제 근무시간과 야근시간을 계산하는 일이 생각보다 번거롭습니다. 특히 직원마다 출퇴근시간이 다르거나 자정을 넘겨 근무하는 경우에는 계산 실수가 발생하기 쉽습니다.
엑셀과 챗GPT를 함께 활용하면 출근시간, 퇴근시간, 휴게시간만 입력해도 실제 근무시간과 연장근로시간, 예상 수당을 자동으로 계산하는 근태관리표를 만들 수 있습니다.
이번 글에서는 엑셀 초보자도 따라 할 수 있도록 실제 근무기록을 활용해 계산표를 만들고, 챗GPT로 수식을 작성하는 방법까지 알아보겠습니다.
1. 엑셀 근무시간 자동 계산표 만들기
먼저 엑셀을 실행하고 다음과 같이 근무기록표를 작성합니다.
| 열 | 항목 | 입력 예시 |
|---|---|---|
| A | 근무일자 | 2026-10-26 |
| B | 직원명 | 김철수 |
| C | 출근시간 | 09:00 |
| D | 퇴근시간 | 19:00 |
| E | 휴게시간(시간) | 1 |
| F | 실제 근무시간 | 자동 계산 |
| G | 연장근로시간 | 자동 계산 |
| H | 시급 | 12,000 |
| I | 기본급 계산액 | 자동 계산 |
| J | 연장근로수당 | 자동 계산 |
| K | 합계 금액 | 자동 계산 |
A열부터 K열까지 첫 번째 행에 항목명을 입력하고, 두 번째 행부터 직원별 근무기록을 입력합니다.
출근시간과 퇴근시간은 반드시 엑셀의 시간 형식으로 입력해야 합니다. 휴게시간은 30분이면 0.5, 1시간이면 1로 입력합니다.
여기서는 계산 원리를 이해하기 위해 하루 실제 근무시간 8시간을 기준으로 연장근로시간을 구합니다. 실제 임금계산에서는 근로자 유형, 소정근로시간, 주간 근로시간 및 법정 가산수당 적용 여부를 별도로 확인해야 합니다.
2. 출퇴근시간으로 실제 근무시간 계산하기
출근시간이 오전 9시이고 퇴근시간이 오후 7시라면 회사에 머문 시간은 10시간입니다.
이 중 휴게시간 1시간을 제외하면 실제 근무시간은 9시간입니다.
F2 셀에 다음 수식을 입력합니다.
=MOD(D2-C2,1)*24-E2
수식의 의미는 다음과 같습니다.
- D2-C2 : 퇴근시간에서 출근시간을 뺀 값
- MOD(…,1) : 자정을 넘긴 경우에도 시간 차이를 계산
- ×24 : 엑셀 시간값을 시간 단위 숫자로 변환
- -E2 : 휴게시간 제외
예를 들어 오후 10시에 출근하여 다음 날 오전 6시에 퇴근하고 휴게시간이 1시간이라면 실제 근무시간은 7시간입니다.
다만 이 수식은 한 번의 근무가 24시간 미만이라는 전제에서 사용합니다. 퇴근시간을 잘못 입력해도 계산값이 나올 수 있으므로 입력값을 확인해야 합니다.
3. 하루 8시간 초과 근무시간 계산하기
실제 근무시간을 계산했다면 다음으로 연장근로시간을 구합니다.
하루 8시간을 초과한 시간을 별도로 표시하려면 G2 셀에 다음 수식을 입력합니다.
=MAX(F2-8,0)
예를 들어 실제 근무시간이 9시간이라면 계산 결과는 1시간입니다.
| 실제 근무시간 | 8시간 초과분 |
|---|---|
| 7시간 | 0시간 |
| 8시간 | 0시간 |
| 9시간 | 1시간 |
| 10시간 | 2시간 |
| 11시간 | 3시간 |
MAX 함수는 계산 결과가 음수일 때 0을 표시합니다.
따라서 8시간 미만으로 근무한 직원에게 연장근로시간이 음수로 표시되는 문제를 방지할 수 있습니다.
주의할 점은 하루 8시간 초과분이 모든 근로자의 법정 연장근로시간과 항상 일치하지는 않는다는 것입니다. 주 40시간 초과 여부, 단시간근로자의 소정근로시간 초과 여부 등도 고려해야 합니다.
4. 야근수당 자동 계산하는 방법
근로기준법상 연장근로에 대한 가산수당은 일반적으로 통상임금의 50% 이상입니다. 다만 상시근로자 5인 이상 사업장인지, 어떤 근로시간 제도가 적용되는지 등에 따라 계산 조건이 달라집니다.
다음 예제는 가산수당 규정이 적용되는 근로자가 평일에 8시간을 정상 근무하고 추가로 1시간 연장근로한 경우입니다.
시급은 12,000원으로 가정합니다.
연장근로 1시간의 수당 계산은 다음과 같습니다.
12,000원 × 1시간 × 1.5 = 18,000원
엑셀 J2 셀에는 다음 수식을 입력합니다.
=G2*H2*1.5
기본급 계산액은 I2 셀에 다음 수식을 입력합니다.
=MIN(F2,8)*H2
마지막으로 K2 셀에 기본급과 연장근로수당을 합산합니다.
=I2+J2
| 구분 | 계산 결과 |
|---|---|
| 실제 근무시간 | 9시간 |
| 기본 근무시간 | 8시간 |
| 연장근로시간 | 1시간 |
| 시급 | 12,000원 |
| 기본급 계산액 | 96,000원 |
| 연장근로수당 | 18,000원 |
| 합계 금액 | 114,000원 |
이 계산은 설명을 위한 단순 예제입니다. 실제 급여에서는 통상시급, 주휴수당, 월급에 포함된 수당, 야간·휴일근로 중복 여부 등을 확인해야 합니다.
또한 법에서 말하는 야간근로는 오후 10시부터 다음 날 오전 6시까지의 근로입니다. 따라서 일반적으로 말하는 야근과 법정 야간근로는 구분해야 합니다.
5. 챗GPT로 근무시간 계산 수식 만들기
엑셀 수식을 직접 작성하기 어렵다면 챗GPT에 계산 조건을 설명하고 필요한 수식을 요청할 수 있습니다.
다음 문장을 그대로 입력해 봅니다.
챗GPT 입력 예시
“엑셀에서 C열은 출근시간, D열은 퇴근시간, E열은 휴게시간, F열은 실제 근무시간, G열은 하루 8시간 초과 근무시간입니다. 자정을 넘기는 근무도 계산할 수 있도록 F2와 G2에 들어갈 엑셀 수식을 만들어 주세요. 휴게시간은 시간 단위 숫자로 입력합니다. 각 수식의 의미도 초보자가 이해하기 쉽게 설명해 주세요.”
챗GPT는 입력한 조건에 맞춰 다음과 같은 수식을 제안할 수 있습니다.
F2 실제 근무시간
=MOD(D2-C2,1)*24-E2
G2 하루 8시간 초과 근무시간
=MAX(F2-8,0)
이렇게 작성한 수식을 엑셀에 붙여넣고 아래쪽 행까지 복사하면 여러 직원의 근무시간을 자동으로 계산할 수 있습니다.
챗GPT에 회사의 근무형태와 수당 계산 기준을 구체적으로 알려줄수록 필요한 수식을 만들기 쉽습니다. 다만 생성된 수식이 실제 법정 수당 계산에 적합한지는 별도로 검증해야 합니다.
6. 여러 직원의 근무시간과 수당 계산 실습
직원 4명의 근무기록을 입력했다고 가정하겠습니다.
시급은 모두 12,000원이며, 각 직원의 해당 근무일이 통상적인 평일이고 법정 연장근로 가산수당이 적용되는 상황입니다.
| 직원명 | 출근 | 퇴근 | 휴게 | 실근무 | 8시간 초과 |
|---|---|---|---|---|---|
| 김철수 | 09:00 | 18:00 | 1 | 8 | 0 |
| 이영희 | 09:00 | 19:00 | 1 | 9 | 1 |
| 박민수 | 08:30 | 20:30 | 1 | 11 | 3 |
| 최지은 | 10:00 | 19:00 | 1 | 8 | 0 |
각 직원의 계산 결과는 다음과 같습니다.
| 직원명 | 기본급 계산액 | 연장근로수당 | 합계 |
|---|---|---|---|
| 김철수 | 96,000원 | 0원 | 96,000원 |
| 이영희 | 96,000원 | 18,000원 | 114,000원 |
| 박민수 | 96,000원 | 54,000원 | 150,000원 |
| 최지은 | 96,000원 | 0원 | 96,000원 |
| 합계 | 384,000원 | 72,000원 | 456,000원 |
직원별 합계 금액이 K2부터 K5까지 입력되어 있다면 전체 금액은 다음 수식으로 계산합니다.
=SUM(K2:K5)
결과는 456,000원입니다.
연장근로수당만 합산하려면 다음 수식을 사용합니다.
=SUM(J2:J5)
결과는 72,000원입니다.
이처럼 한 번 수식을 설정하면 직원 수가 늘어나더라도 같은 계산 방식을 적용할 수 있습니다.
7. 오후 10시 이후 야간근로수당 계산하기
야간근로수당은 단순히 하루 8시간을 초과했다고 발생하는 수당이 아닙니다.
근로기준법상 야간근로에 해당하는 오후 10시부터 다음 날 오전 6시까지의 실제 근로시간을 기준으로 계산해야 합니다.
예를 들어 오후 9시에 출근해 다음 날 오전 1시에 퇴근하고, 휴게시간 없이 4시간 근무했다면 다음과 같습니다.
| 항목 | 시간 |
|---|---|
| 출근 | 21:00 |
| 퇴근 | 다음 날 01:00 |
| 실제 근무 | 4시간 |
| 야간근로 | 3시간 |
| 시급 | 12,000원 |
기본임금은 48,000원입니다.
야간근로 가산분은 다음과 같습니다.
12,000원 × 3시간 × 0.5 = 18,000원
따라서 예제의 총액은 66,000원입니다.
야간근로 가산분만 계산하는 수식은 야간근로시간을 L2 셀에 입력했다고 가정하면 다음과 같습니다.
=L2*H2*0.5
여기서 L2는 오후 10시부터 오전 6시 사이에 실제로 근무한 시간입니다.
야간시간에 휴게시간이 포함되어 있다면 반드시 제외해야 합니다. 연장근로와 야간근로가 겹치는 경우에는 적용 요건에 따라 각각의 가산분을 반영해야 합니다.
8. 엑셀 계산에서 자주 발생하는 오류 해결하기
근무시간 계산표를 만들 때 자주 발생하는 문제는 다음과 같습니다.
| 문제 | 원인 | 해결 방법 |
|---|---|---|
| 계산 결과가 날짜로 표시됨 | 셀 표시 형식 오류 | 숫자 형식으로 변경 |
| 근무시간이 0.375로 표시됨 | 시간값을 일 단위로 표시 | 24를 곱해 시간으로 변환 |
| 자정 이후 근무가 잘못 계산됨 | 날짜 변경 미반영 | MOD 함수 사용 |
| 수식이 계산되지 않음 | 셀이 텍스트 형식 | 일반 형식으로 변경 후 재입력 |
| 수당이 예상과 다름 | 시급 또는 가산 기준 오류 | 통상시급과 적용 조건 확인 |
특히 출근시간과 퇴근시간은 09:00, 18:30처럼 입력해야 합니다.
9시, 오후 6시 반 등의 문자로 입력하면 엑셀에서 시간 계산이 제대로 되지 않을 수 있습니다.
또한 근무시간을 숫자로 계산하는 F열과 G열은 숫자 형식으로 설정하고, 수당을 계산하는 I열부터 K열은 통화 또는 숫자 형식으로 설정하는 것이 좋습니다.
9. 월별 근무시간과 수당 자동 합산하기
일별 근무시간 계산표를 만들었다면 월별 합계도 쉽게 구할 수 있습니다.
예를 들어 F2부터 F31까지 실제 근무시간이 기록되어 있다면 다음 수식을 사용합니다.
월 총근무시간
=SUM(F2:F31)
월 8시간 초과 근무시간 합계
=SUM(G2:G31)
월 연장근로수당 합계
=SUM(J2:J31)
월 계산 금액 합계
=SUM(K2:K31)
직원이 여러 명이라면 SUMIF 함수를 이용해 특정 직원의 근무시간만 합산할 수도 있습니다.
예를 들어 B열에 직원명이 있고 F열에 실제 근무시간이 입력되어 있다면 다음 수식을 사용합니다.
=SUMIF(B2:B100,"김철수",F2:F100)
이 수식은 김철수라는 이름으로 기록된 근무시간만 합산합니다.
동명이인이 있는 사업장에서는 이름 대신 사번을 사용하는 것이 정확합니다.
이렇게 하면 매월 직원별 근무시간을 일일이 계산하지 않아도 근무기록을 바탕으로 합계를 확인할 수 있습니다.
다만 월별 총근무시간이나 하루 8시간 초과분의 합계만으로 법정 연장근로수당이 확정되는 것은 아닙니다. 주별 근로시간과 근무형태도 함께 검토해야 합니다.
10. 실제 급여 계산 전 반드시 확인할 사항
엑셀에서 계산 결과가 정확하게 표시되더라도 법정 수당까지 정확하게 산정되었다는 의미는 아닙니다.
실제 급여 계산에는 다음 사항을 확인해야 합니다.
- 상시근로자 수에 따른 근로기준법 적용 범위
- 근로계약서에 정해진 소정근로시간
- 하루 및 일주일 단위 연장근로 여부
- 오후 10시부터 오전 6시까지의 야간근로시간
- 휴일근로 여부와 가산수당 적용 기준
- 휴게시간과 실제 근로시간의 구분
- 통상임금에 해당하는 시급 산정 기준
특히 상시근로자 5인 미만 사업장에서는 근로기준법상 연장·야간·휴일근로 가산수당 규정이 일반적으로 적용되지 않습니다. 다만 근로계약이나 별도 약정에 따라 수당을 지급해야 하는 경우가 있을 수 있습니다.
또한 단시간근로자는 소정근로시간을 초과한 근로에 대해 별도의 가산수당 규정이 적용될 수 있습니다.
따라서 이번 예제는 근무시간 계산과 수식 자동화 방법을 익히는 용도로 활용하고, 실제 임금 지급 전에는 사업장에 적용되는 법령과 근로계약을 확인해야 합니다.
11. 챗GPT를 활용해 근태관리표 확장하기
기본 계산표가 완성되었다면 챗GPT를 이용해 더 다양한 기능을 추가할 수 있습니다.
예를 들어 직원별 월간 근무시간 집계, 지각 및 조퇴시간 계산, 부서별 인건비 분석, 연장근로시간 경고 표시 등을 구현할 수 있습니다.
다음과 같이 요청하면 됩니다.
챗GPT 입력 예시
“현재 엑셀 근태관리표에는 근무일자, 직원명, 출근시간, 퇴근시간, 휴게시간, 실제 근무시간, 연장근로시간, 시급, 수당이 있습니다. 직원별 월간 근무시간과 연장근로수당을 자동 집계하고, 주간 근무시간도 확인할 수 있는 요약표를 만들어 주세요. 필요한 엑셀 함수와 셀 위치를 구체적으로 설명해 주세요.”
이렇게 하면 단순한 출퇴근 기록표를 직원별 근무현황과 인건비를 관리하는 자료로 확장할 수 있습니다.
12. 마무리
챗GPT와 엑셀을 함께 활용하면 복잡해 보이는 근무시간 계산도 비교적 쉽게 자동화할 수 있습니다.
출근시간과 퇴근시간을 입력하면 실제 근무시간을 계산하고, 하루 8시간 초과분을 확인하며, 예제에서 정한 시급과 가산율을 적용해 예상 수당까지 산출할 수 있습니다.
처음에는 직원 한 명의 근무기록으로 수식을 검증한 뒤 여러 직원에게 확대 적용하는 것이 좋습니다.
특히 근무시간 자동 계산과 법정 임금 산정은 구분해야 합니다. 정확한 근무기록을 유지하고 사업장별 적용 기준을 반영하면 엑셀 근태관리표를 보다 실용적으로 활용할 수 있습니다.
답글 남기기