엑셀 근무시간 계산 안 맞을 때: 출근시간·퇴근시간 빼기와 야간근무 초보 공식

엑셀 근무시간 계산 안 맞음으로 검색하는 경우를 보면, 대부분 출근시간에서 퇴근시간을 빼는 것까지는 했는데 결과가 이상하게 나오는 상황입니다. 특히 퇴근시간 빼기, 휴게시간 제외, 야간근무처럼 자정을 넘기는 근무가 섞이면 초보자 입장에서는 어디서 틀렸는지 찾기가 꽤 어렵습니다.

이번 글에서는 실제 근태표에서 바로 만나는 구조로 설명해 보겠습니다. 어려운 기능 없이 IF, SUM, SUMIF 정도만 사용해서 출근시간과 퇴근시간을 계산하고, 휴게시간을 빼고, 직원별 합계까지 확인하는 흐름입니다.

문제 상황: 18:00에서 09:00을 빼면 되는데 왜 합계가 이상할까

아래처럼 근태표를 만든 상황이라고 해보겠습니다. A2:H8 범위에 근무 내역이 있고, 실제 데이터는 3행부터 8행까지 입력되어 있습니다.

A열은 근무일, B열은 사원명, C열은 출근시간, D열은 퇴근시간, E열은 휴게분입니다. F열에는 총근무시간, G열에는 휴게시간을 뺀 인정근무시간, H열에는 초과시간을 계산할 예정입니다.

초보자가 가장 많이 하는 수식은 F3 셀에 단순히 =D3-C3를 입력하는 방식입니다. 일반 주간 근무에서는 어느 정도 맞아 보이지만, 22:00에 출근해서 다음 날 06:00에 퇴근한 행에서는 음수처럼 처리되거나, #### 표시가 뜨거나, 합계가 맞지 않는 문제가 생깁니다.

원인: 엑셀에서 시간은 ‘하루를 1로 보는 숫자’라서 헷갈린다

엑셀의 시간은 겉으로는 09:00처럼 보이지만 내부적으로는 숫자입니다. 하루 24시간을 1로 보기 때문에 12:00은 0.5, 06:00은 0.25처럼 저장됩니다.

그래서 같은 날 09:00부터 18:00까지는 18:00이 09:00보다 뒤에 있으니 단순히 빼도 됩니다. 하지만 22:00부터 다음 날 06:00까지는 화면상 퇴근시간 06:00이 출근시간 22:00보다 작아 보입니다. 엑셀 입장에서는 “퇴근이 출근보다 빠른데?”라고 판단하는 셈입니다.

또 하나 헷갈리는 부분은 E열의 휴게분입니다. E3에 60이라고 적었다면 이것은 60분이라는 뜻이지, 엑셀 시간 1시간이 아닙니다. 엑셀 시간 계산에서 60분을 빼려면 60/1440으로 바꿔서 계산해야 합니다. 하루가 1440분이기 때문입니다.

해결 흐름: 자정 넘김은 IF로 먼저 잡고, 휴게분은 1440으로 나눈다

먼저 F3 셀에 총근무시간을 구하는 수식을 입력합니다. F3은 첫 번째 데이터 행의 결과 셀입니다. 입력한 뒤 F8까지 아래로 복사하면 됩니다.

=IF(D3<C3,D3+1-C3,D3-C3)

이 수식의 뜻은 어렵지 않습니다. 퇴근시간 D3이 출근시간 C3보다 작으면 자정을 넘긴 근무로 보고, 퇴근시간에 1일을 더한 뒤 출근시간을 뺍니다. 그렇지 않으면 평소처럼 퇴근시간에서 출근시간을 뺍니다.

예를 들어 C5가 22:00, D5가 06:00이라면 D5가 C5보다 작습니다. 이때 D5에 1을 더해서 다음 날 06:00으로 보고 계산하므로 총근무시간은 8:00이 됩니다.

다음으로 G3 셀에는 휴게시간을 제외한 인정근무시간을 계산합니다. E열은 ‘분’ 단위로 입력되어 있으므로 1440으로 나눠야 엑셀 시간으로 바뀝니다.

=F3-E3/1440

G3에 입력한 뒤 G8까지 복사합니다. 예를 들어 F3이 9:00이고 E3이 60이면, 인정근무시간은 8:00이 됩니다. 만약 휴게분을 1:00처럼 시간 형태로 입력해 둔 파일이라면 이 수식은 맞지 않습니다. 이 글의 예시는 E열에 60, 30처럼 숫자 분 단위로 입력하는 기준입니다.

초과시간까지 보고 싶다면 기준근무시간을 따로 셀에 넣어두는 편이 안전합니다. 예를 들어 K2 셀에 기준근무시간으로 8:00을 입력합니다. 그리고 H3 셀에는 아래 수식을 입력합니다.

=IF(G3>$K$2,G3-$K$2,0)

$K$2처럼 달러 표시를 붙이면 수식을 아래로 복사해도 기준근무시간 셀이 움직이지 않습니다. 초보자분들이 자주 놓치는 부분인데, H3에서 H8까지 복사할 때 K2가 K3, K4로 밀리면 초과시간 계산이 갑자기 이상해집니다.

표시 형식도 꼭 확인해야 한다: 8:00이 0.333333으로 보일 수 있다

수식은 맞는데 결과가 0.333333, 0.375처럼 보이는 경우가 있습니다. 이것은 계산이 틀린 것이 아니라 셀 서식이 시간으로 되어 있지 않아서 그렇습니다.

F3:H8 범위를 선택한 뒤 Ctrl + 1을 누릅니다. 셀 서식 창에서 표시 형식 > 사용자 지정으로 들어가고, 형식 입력 칸에 아래처럼 입력합니다.

[h]:mm

여기서 대괄호가 중요합니다. 그냥 h:mm으로 설정하면 합계가 24시간을 넘을 때 다시 0시부터 돌아가는 것처럼 보일 수 있습니다. 근무시간 합계처럼 24시간을 넘길 수 있는 값은 [h]:mm 형식을 쓰는 것이 안전합니다.

검증 방법: 한 줄씩 손으로 계산해 보고 합계를 맞춰본다

수식을 넣은 뒤에는 바로 전체 합계만 보지 말고, 몇 줄을 손으로 먼저 확인해 보세요. 특히 야간근무가 있는 행과 휴게시간이 30분인 행을 골라보면 오류를 빨리 찾을 수 있습니다.

전체 인정근무시간 합계는 예를 들어 K4 셀에 표시할 수 있습니다. K4 셀에 아래 수식을 입력하고, K4의 표시 형식도 [h]:mm으로 바꿔 주세요.

=SUM(G3:G8)

초과시간 합계가 필요하면 K5 셀에 아래 수식을 입력합니다.

=SUM(H3:H8)

합계가 예상보다 작거나 0으로 나온다면 F:H열의 표시 형식뿐 아니라 C:D열의 출근시간, 퇴근시간이 진짜 시간인지도 확인해야 합니다. 시간이 왼쪽 정렬되어 있거나, 앞에 작은따옴표가 붙어 있으면 텍스트일 가능성이 큽니다.

흔한 실수: 9.00, 900, 09시 입력은 시간으로 계산되지 않을 수 있다

엑셀에서 시간은 보통 09:00처럼 콜론을 넣어 입력해야 합니다. 9.00은 소수 숫자 9로 해석될 수 있고, 900은 그냥 숫자 900입니다. 09시처럼 한글이 섞이면 텍스트가 되어 계산이 안 되는 경우가 많습니다.

이미 받은 파일의 C열, D열이 텍스트 시간이라면 먼저 C:D열을 선택해 보세요. 셀이 왼쪽으로 붙어 있거나, 수식 입력줄에서 앞에 작은따옴표가 보이면 텍스트일 수 있습니다.

이럴 때는 C:D열을 선택한 뒤 데이터 탭 > 텍스트 나누기를 누르고, 특별히 나눌 구분 기호가 없으면 다음, 다음을 지나 마지막에 마침을 눌러 보세요. 단순한 텍스트 시간이 실제 시간값으로 바뀌는 경우가 많습니다. 바꾼 뒤에는 반드시 F열 수식 결과가 달라졌는지 확인해야 합니다.

응용: 직원명을 조건으로 인정근무시간 합계 구하기

근태표를 만들다 보면 전체 합계보다 직원별 합계가 더 필요할 때가 많습니다. 예를 들어 K8 셀에 조회할 사원명을 입력한다고 해보겠습니다. K8에 김민수라고 입력하고, K9 셀에 김민수의 인정근무시간 합계를 표시합니다.

이때는 SUMIF를 쓰면 됩니다. B3:B8에서 K8의 사원명과 같은 행을 찾고, 그 행의 G3:G8 인정근무시간을 더하는 방식입니다.

=SUMIF($B$3:$B$8,$K$8,$G$3:$G$8)

K9 셀도 표시 형식을 [h]:mm으로 바꿔야 합니다. 수식은 맞는데 1.25 같은 숫자로 보이면 서식 문제입니다.

여기서 범위를 고정하는 달러 표시도 중요합니다. 나중에 여러 직원명을 아래로 쭉 적고 수식을 복사할 예정이라면, 원본 범위인 B3:B8과 G3:G8은 움직이지 않아야 합니다. 그래서 $B$3:$B$8, $G$3:$G$8처럼 고정해 둡니다.

실무에서 적용할 때 볼 것

근무시간 계산은 수식 자체보다 입력 형태가 더 중요합니다. 출근시간과 퇴근시간은 09:00처럼 시간으로 입력하고, 휴게시간은 이 글처럼 60, 30 같은 분 단위 숫자로 통일해야 합니다. 한 파일 안에서 어떤 행은 1:00, 어떤 행은 60으로 섞이면 계산이 맞을 수 없습니다.

자정을 넘기는 근무가 하나라도 있다면 단순히 퇴근시간에서 출근시간을 빼지 말고, IF(D3<C3,D3+1-C3,D3-C3) 구조로 잡아두는 것이 안전합니다. 그리고 근무시간 결과와 합계 셀은 [h]:mm 표시 형식으로 맞춰 주세요.

처음에는 한두 행만 계산해 보고, 손계산 결과와 맞는지 확인한 뒤 전체 행으로 복사하는 습관을 들이면 좋습니다. 근태표는 한 번 틀리면 급여, 수당, 정산표까지 같이 틀어지기 때문에 작은 표에서 검증하고 확장하는 방식이 가장 안전합니다.