엑셀 근무시간 계산: 야간 교대·휴게시간·누락 기록 검증
AI Editter
22시에 출근해서 다음 날 07시에 퇴근한 기록은 9시간입니다. 하지만 종료 날짜가 빠져 있거나 휴게시간을 소수로 잘못 입력하면 전혀 다른 결과가 나옵니다. 수식이 오류를 표시하지 않아도 근무 기록은 틀릴 수 있습니다.
이 글은 입력된 출퇴근·휴게 기록의 시간 차이를 계산하고 검증하는 방법입니다. 급여나 법정 가산수당을 판정하는 표가 아닙니다. 날짜를 포함한 원본 기록을 사용하며, 시간대 변경이 없는 동일한 지역의 기록을 전제로 합니다.
날짜가 있는 원본과 시각만 있는 원본을 구분
출근이 22:00, 퇴근이 07:00으로만 제공됐다면 다음 날인지, 잘못 입력된 것인지 수식으로 확정할 수 없습니다. 다음 식은 결과를 하루 범위 안으로 돌려놓기 때문에 역전 입력도 정상처럼 만들 수 있습니다.
=MOD(퇴근시각-출근시각,1)24시간 미만의 근무이고 날짜 관계가 이미 확인된 자료에는 사용할 수 있습니다. 그러나 누락과 잘못된 날짜를 찾아야 하는 관리표에는 시작·종료 일시를 모두 저장하는 편이 적합합니다.
| 열 | 내용 | 입력 예 |
|---|---|---|
| A | 기록번호 | SHIFT-301 |
| B | 시작일시 | 2026-10-01 22:00 |
| C | 종료일시 | 2026-10-02 07:00 |
| D | 확인된 휴게분 | 60 |
| E | 입력 상태 | 계산식 |
| F | 순시간 | 계산식 |
휴게는 분 단위 정수로 받으면 0.5를 30분으로 생각하는 실수를 줄일 수 있습니다. 30분은 D열에 30을 입력합니다. 휴게 없음이 확인됐다면 0, 미확인이라면 빈칸으로 구분합니다.
잘못된 행을 먼저 표시한 뒤 시간 계산
아래 예시는 B·C가 실제 날짜 숫자이고 D가 분 단위 숫자인지 확인합니다. 날짜처럼 보이는 문자열은 숫자가 아니므로 원본 변환부터 해야 합니다.
=IF(OR(B2="",C2="",D2=""),"입력 누락",
IF(NOT(AND(ISNUMBER(B2),ISNUMBER(C2),ISNUMBER(D2))),"형식 확인",
IF(C2<B2,"종료일시 확인",
IF(OR(D2<0,D2>(C2-B2)*1440),"휴게분 확인","정상"))))F2에는 정상 행에 대해서만 시간을 계산합니다.
=IF(E2="정상",C2-B2-D2/1440,"확인 필요")F열의 표시 형식은 [h]:mm으로 지정합니다. 시간 합계를 숫자 시간으로 사용해야 한다면 F2에 24를 곱합니다. 8시간 30분은 8.5시간이지 8.30시간이 아닙니다. 날짜·시간의 차이와 누적 표시 방식은 Microsoft 시간 계산 안내, 24시간 이상 표시 안내를 참고합니다.
검사에서 통과했다고 현실적으로 가능한 기록이라는 뜻은 아닙니다. 회사가 정한 점검 기준을 넘는 장시간 기록, 같은 사람의 겹치는 근무, 동일 기록번호 중복은 별도 열에서 확인합니다.
예제에서 반드시 나와야 하는 결과
| 시작 | 종료 | 휴게분 | 기대 결과 |
|---|---|---|---|
| 10-01 09:00 | 10-01 18:00 | 60 | 8:00 |
| 10-01 22:00 | 10-02 07:00 | 60 | 8:00 |
| 10-01 23:50 | 10-02 00:20 | 0 | 0:30 |
| 10-01 22:00 | 10-01 07:00 | 60 | 종료일시 확인 |
| 10-01 09:00 | 10-01 10:00 | 90 | 휴게분 확인 |
| 10-01 09:00 | 빈칸 | 0 | 입력 누락 |
오류 행이 문자로 남아 있으면 SUM은 그 행을 더하지 않을 수 있습니다. 따라서 합계 셀에는 오류 건수가 0인지 함께 표시해야 합니다. 대상 범위가 2~31행이라면 먼저 =COUNTIF(E2:E31,"<>정상")로 확인하되, 사용하지 않는 빈 행까지 범위에 넣지 않도록 표의 실제 기록 범위를 사용합니다. 정상 행 합계만 최종 근무시간처럼 배포하면 누락된 기록이 숨습니다.
월말에 걸친 근무와 휴게 구간
10월 31일 22:00부터 11월 1일 07:00까지를 달력 월별로 나눈다면 10월 2시간, 11월 7시간입니다. 휴게가 11월 1일 02:00~03:00이었다면 순시간은 10월 2시간, 11월 6시간입니다.
반면 휴게 합계 60분만 있고 실제 휴게 시각이 없다면 어느 월에서 빼야 하는지 계산으로 알아낼 수 없습니다. 월별 또는 시간대별 분리가 필요하다면 휴게 시작·종료도 기록합니다.
구간이 겹치는 길이는 다음 원리로 구합니다. 각 일시 셀에는 날짜와 시각이 모두 들어 있어야 합니다.
=MAX(0,MIN(종료일시,기간종료)-MAX(시작일시,기간시작))근무 구간과 집계 기간의 겹침에서, 해당 기간에 걸친 휴게 구간의 겹침을 뺍니다. 휴게 구간이 두 개라면 각각 계산하되 서로 겹친 휴게를 이중 차감하지 않았는지 검사합니다. 어떤 날짜에 근무를 귀속할지는 조직의 기록 기준을 먼저 정해야 합니다.
마감 전에 남길 점검표
기록번호 중복 0건, 시작·종료 누락 0건, 음수 구간 0건, 휴게가 근무를 벗어난 행 0건, 담당자 미확인 행 0건을 확인합니다. “0건”은 검사한 기록이 존재할 때만 의미가 있습니다. 아예 그달 파일이 도착하지 않은 사람은 제출 명단과 대조해야 합니다.
수정 이력을 남길 때는 원본을 덮어쓰지 말고 기록번호, 수정 전후 값, 수정 사유, 확인자를 보관합니다. 날짜가 잘못된 한 행을 고친 것인지, 집계 기준을 바꾼 것인지 구별할 수 있어야 다음 달에도 같은 계산을 유지할 수 있습니다.