엑셀 근무시간 계산, 합계가 이상하게 나오는 진짜 이유

출근 시간과 퇴근 시간을 빼서 근무시간을 구했습니다. 하루하루는 맞는데 월 합계가 터무니없이 작게 나옵니다. 180시간이 나와야 하는데 12:00 같은 숫자가 찍혀 있습니다.

계산이 틀린 게 아닙니다. 엑셀이 시간을 저장하는 방식을 모르고 서식을 그대로 둔 탓입니다. 이것 하나만 이해하면 근태 계산에서 막히는 일이 거의 없어집니다.


먼저 알아야 할 것. 시간은 소수다

엑셀에서 하루가 1입니다. 그래서 09:00은 0.375, 12:00은 0.5, 18:00은 0.75로 저장됩니다. 화면에 보이는 09:00은 표시 형식일 뿐입니다.

여기서 문제가 생깁니다. 근무시간을 다 더해서 30시간이 되면 실제 값은 1.25입니다. 일반 시간 서식은 1(하루)을 넘어가는 부분을 버리고 소수점 이하만 보여주기 때문에 6:00으로 표시됩니다.

계산은 처음부터 맞았고, 보여주는 방식만 틀렸던 겁니다.

1단계. 하루 근무시간 구하기

출근이 B열, 퇴근이 C열이라면 기본은 뺄셈입니다.

=C2-B2

그런데 야간 근무처럼 자정을 넘기면 결과가 음수가 되어 #####으로 채워집니다. 22:00 출근, 06:00 퇴근이면 0.25 - 0.9166이 되기 때문입니다.

이때는 MOD를 씁니다.

=MOD(C2-B2, 1)

음수가 나오면 1(하루)을 더해주는 역할입니다. 자정을 넘기든 안 넘기든 모두 정상 작동하므로, 처음부터 MOD를 쓰는 편이 안전합니다.

2단계. 휴게시간 빼기

점심시간이 고정 1시간이라면 이렇게 씁니다.

=MOD(C2-B2, 1) - TIME(1,0,0)

TIME(1,0,0)은 1시간을 엑셀이 아는 값(0.041666...)으로 바꿔줍니다. -1이라고 쓰면 하루를 빼버리므로 절대 안 됩니다.

근무시간에 따라 휴게시간이 달라진다면 IF로 나눕니다.

=MOD(C2-B2,1) - IF(MOD(C2-B2,1)>=TIME(8,0,0), TIME(1,0,0), TIME(0,30,0))

3단계. 초과근무 시간만 뽑기

실근무가 D열이고 소정근로가 8시간이라면,

=MAX(0, D2 - TIME(8,0,0))

MAX(0, ...)로 감싸는 이유는 8시간을 못 채운 날 음수가 나오는 걸 막기 위해서입니다. 이 처리를 빼먹으면 월 합계에서 초과근무가 상쇄되어 실제보다 적게 나옵니다.

4단계. 합계 서식 바꾸기 (가장 중요)

합계 셀을 선택하고 Ctrl + 1 → 표시 형식 → 사용자 지정 → 형식 칸에 직접 입력합니다.

[h]:mm

대괄호가 핵심입니다. "24시간에서 끊지 말고 계속 세라"는 뜻입니다. 이 대괄호 하나로 6:00이 30:00으로 바뀝니다.

분까지 필요 없다면 [h]"시간", 초까지 보려면 [h]:mm:ss를 쓰면 됩니다.

5단계. 급여 계산용 숫자로 바꾸기

시급을 곱하려면 시간 값이 아니라 숫자가 필요합니다. 하루가 1이므로 24를 곱하면 시간 단위 숫자가 됩니다.

=D2*24

30시간이면 30이 나옵니다. 여기에 시급을 곱하면 됩니다. 결과 셀의 표시 형식을 반드시 '일반' 또는 '숫자'로 바꾸세요. 시간 서식이 남아 있으면 30이 다시 시간으로 해석되어 이상한 값이 나옵니다.

15분 단위로 끊어야 한다면 FLOOR를 씁니다.

=FLOOR(D2*24, 0.25)

자주 막히는 지점 세 가지

① 시간을 더했는데 값이 안 더해짐

시간이 텍스트로 저장된 경우입니다. 다른 시스템에서 내려받은 근태 파일에서 흔합니다. 값이 셀 왼쪽에 붙어 있으면 텍스트입니다.

=TIMEVALUE(B2)

0900처럼 콜론 없이 들어온 경우라면 먼저 형태를 만들어야 합니다.

=TIMEVALUE(LEFT(TEXT(B2,"0000"),2)&":"&RIGHT(TEXT(B2,"0000"),2))

② 날짜에 시각이 섞여 있음

2026-01-04 09:31처럼 날짜와 시각이 한 셀에 들어 있으면, 뺄셈 결과에 날짜 차이까지 포함됩니다. 같은 날 안에서만 계산할 거라면 시각만 떼어냅니다.

=B2-INT(B2)

반대로 날짜만 남기려면 =INT(B2)입니다.

③ 합계는 맞는데 소수점이 미세하게 어긋남

시간은 대부분 무한소수라서 반올림 오차가 쌓입니다. 1분 단위로 정리하면 사라집니다.

=ROUND(D2*1440, 0)/1440

1440은 하루의 분 수입니다.

자주 묻는 질문

Q. 야간수당(22시~06시) 시간만 따로 뽑을 수 있나요?

가능하지만 수식이 길어집니다. 근무 구간과 야간 구간이 겹치는 부분을 구하는 방식이라 MIN과 MAX를 조합해야 하고, 자정을 넘기는 경우까지 넣으면 조건이 복잡해집니다. 인원이 많다면 이 계산은 별도 열로 단계를 나눠서 만드는 편이 검증하기 쉽습니다.

Q. 주 52시간 초과 여부를 자동으로 표시하려면?

주차별로 합계를 낸 뒤 조건부 서식 수식에 =$G2>TIME(52,0,0)을 넣으면 됩니다. 다만 TIME 함수는 24를 넘는 시간을 그대로 받지 못하므로 =52/24로 쓰는 편이 확실합니다.

Q. 근태 데이터가 매달 오는데 매번 다시 만들어야 하나요?

수식을 만든 시트를 템플릿으로 저장해두고 원본만 갈아끼우면 됩니다. 원본 데이터를 표(Ctrl + T)로 만들어두면 행 수가 달라져도 수식이 자동으로 따라옵니다.

Q. 구글 시트에서도 되나요?

MOD, TIME, MAX, TIMEVALUE 모두 동일합니다. 사용자 지정 서식도 [h]:mm을 그대로 씁니다.

정리

  • 하루 = 1. 시간은 소수로 저장됨
  • 뺄셈은 처음부터 MOD(퇴근-출근, 1)
  • 합계 서식은 반드시 [h]:mm
  • 급여 계산은 *24 후 표시 형식을 '숫자'로

관련 글

  • 엑셀 중복 데이터, 지우기 전에 먼저 확인하는 법
  • 두 파일 대사, 눈으로 비교하지 말고 COUNTIF로 끝내기
  • 숫자가 텍스트로 인식될 때 해결법

댓글

댓글 쓰기