직원들의 출퇴근 기록을 구글시트나 엑셀로 관리하다 보면 가장 신경 쓰이는 부분이 근무시간 계산입니다. 출근시간과 퇴근시간만 입력하면 끝일 것 같지만 실제로는 그렇지 않습니다. 점심시간도 빼야 하고, 누구는 연차 쓰고 누구는 반차 쓰고, 게다가 지각이나 연장근무까지 매달 한눈에 보기 좋게 정리해야 하니까요.
처음에는 그냥 빼기 수식 하나만 넣으면 알아서 척척 계산될 줄 알았는데, 막상 시트를 짜다 보면 예상치 못한 복병을 만나게 됩니다. 출퇴근 시간을 깜빡하고 안 적은 날에는 시트에 #VALUE! 같은 오류가 표시되기도 하고, 휴가인 날에도 엉뚱하게 근무시간이 계산되기도 하죠.
그래서 실무에서 잔오류 없이 깔끔하게 돌아가는 근태관리 시트를 만들려면 몇 가지 필수적인 근무시간 계산 함수를 조합해야 합니다. 이번 글에서는 단순한 함수 나열이 아니라, 실무에서 마주치는 상황별로 출퇴근 계산부터 월별 집계까지 아주 쉽게 풀어보겠습니다.
근무시간 계산이 생각보다 까다로운 이유
자, 퇴근시간에서 출근시간을 빼면 근무시간이 바로 나올까요? 이론적으로는 맞지만, 실제 직원들의 출퇴근 데이터는 그리 단순하지 않습니다.
중간에 있는 점심시간이나 휴게시간을 자동으로 걷어내야 하고, 깜빡하고 출퇴근 기록을 누락 한 날도 예외 처리를 해줘야 합니다. 여기에 "이 직원이 오늘 지각을 했는지", "오늘 연장근무는 몇 시간이나 했는지"까지 사람이 하나씩 확인하다 보면 시간이 꽤 많이 걸립니다. 변수가 워낙 많다 보니 단순한 빼기 수식만으로는 실무용 시트를 만들기 어려운 것이죠. 그럼 가장 기초적인 첫 단계부터 어떻게 문제를 해결할 수 있는지 알아볼까요?
출근시간과 퇴근시간으로 근무시간 계산하기
출근시간과 퇴근시간만 빼면 끝일 것 같지만 실제로는 그렇지 않습니다. 처음 수식을 만들면 9시간 대신 0.375 같은 난해한 소수가 나와서 당황하는 경우가 많은데요. 구글시트와 엑셀은 하루(24시간)를 숫자 1로 저장하기 때문입니다.
여기서 사용하는 것은 특별한 함수가 아니라 시간을 서로 빼는 기본 계산식입니다. 다만 계산 결과는 '시간'이 아니라 하루를 기준으로 한 비율로 표시됩니다. 따라서 우리가 익숙한 시간 단위 숫자로 바꾸려면 계산 결과에 *24를 곱해줘야 합니다. 출근시간이 C5 셀, 퇴근시간이 D5 셀이라면 아래와 같이 입력하면 됩니다.
=(D5-C5)*24
예시 이미지처럼 *24를 곱하지 않으면 0.375로 표시되던 값이, 수식을 적용하는 순간 우리가 원하는 정수 9(시간)로 깔끔하게 떨어지는 것을 볼 수 있습니다.
이렇게 하면 오전 9시 출근, 오후 6시 퇴근을 기준으로 정확한 총 체류 시간을 구할 수 있습니다. 다만 이 결과는 아직 점심시간이나 휴게시간이 포함된 상태입니다. 다음 단계에서는 실무 필수 과정인 '휴게시간을 제외한 실제 근무시간'을 계산해 보겠습니다.
휴게시간을 제외한 실제 근무시간 계산하기
이제 휴게시간을 빼보겠습니다. 점심시간을 1시간 사용했다면 그 시간만 제외하면 됩니다. 만약 E5 셀에 휴게시간이 입력되어 있다면 직관적으로는 아래처럼 식을 세울 수 있습니다.
=(D5-C5)*24-E5오전 9시 출근, 오후 6시 퇴근(9시간)에서 휴게시간 1시간을 빼니 딱 8시간이 남습니다. 그런데 만약 어떤 직원이 출근 도장을 안 찍었거나 그날 연차를 썼다면 어떻게 될까요? 수식이 빈 셀을 계산하려다 에러를 뿜거나 음수(-)를 뱉어내며 시트가 지저분해집니다.
먼저는 이처럼 휴게시간만 빼는 간단한 수식으로 시작하고, 이후 빈 셀이나 휴가를 고려해 IF와 OR, MAX 함수를 추가하면 실무에서도 잔오류 없이 완벽하게 사용할 수 있는 형태가 됩니다.
실무에서는 오류를 막기 위해 IF와 OR, 그리고 MAX 함수를 함께 사용해 아래처럼 최종 수식을 완성합니다. I5 셀에 적힌 휴가 종류가 '없음'이 아니거나 출퇴근 시간이 비어있을 땐 아예 빈칸으로 두고, 혹시 모를 음수 계산 결과는 0으로 딱 잡아주는 안전한 구조입니다.
=IF(OR($I5<>"없음",$C5="",$D5=""),"",MAX(0,($D5-$C5)*24-$E5))지각 여부 자동 판별하기
근무시간 계산이 끝났다면 대부분은 지각 여부도 함께 관리하고 싶어집니다. 매일 아침 직원이 9시 넘어서 왔는지 눈으로 일일이 대조할 필요 없이, 구글시트가 알아서 '지각' 텍스트를 띄워주면 얼마나 편할까요? 이럴 때 쓰는 게 바로 IF와 시간을 만들어주는 TIME 함수입니다.
=IF(C5>TIME(9,0,0),"지각","정상")
출근시간(C5)이 오전 9시 정각보다 크면 자동으로 지각을 띄우고, 같거나 빠르면 정상으로 표시하는 원리입니다. 출근 기준이 9시 30분인 회사라면 TIME(9,30,0)으로 변경해 주면 되죠.
출근시간이 오전 9시를 넘으면 자동으로 '지각'을 표시하며, 휴가나 출근시간 미입력인 경우에는 결과를 표시하지 않도록 설정했습니다.
근태관리 시트에서는 역시나 '휴가 쓴 날'이나 '출근 기록이 없는 날'까지 고려해야 하므로, 앞에 방어막 수식을 조금 더 보완해서 이렇게 적용해 둡니다.
=IF(OR($I5<>"없음",$C5=""),"",IF($C5>TIME(9,0,0),"지각","정상"))연장근무 시간 자동 계산하기
하루 소정 근로시간인 8시간을 꽉 채우고 더 일한 직원들에게는 연장근무 시간을 달아주어야 합니다. 만약 10시간을 일했다면 2시간이 연장근무가 되겠죠. 단순하게 생각하면 근무시간 - 8을 하면 될 것 같지만, 7시간만 일한 조기 퇴근자는 7 - 8 = -1이라는 결과가 나옵니다.
이때 MAX 함수 하나로 아주 쉽게 해결할 수 있습니다. 0과 계산된 값 중 무조건 큰 값만 가져오게 만드는 것이죠.
=MAX(F5-8,0)
이렇게 하면 10시간 일한 날은 2를 반환하지만, 7시간 일해서 마이너스가 나오는 날에는 알아서 0을 반환해 줍니다. 마찬가지로 근무 기록이 아예 없는 날 시트 가득 숫자 0이 도배되는 걸 막기 위해, 실무에서는 IF를 씌워 빈칸 처리를 해줍니다.
근무시간이 8시간이면 연장근무는 0시간으로 표시되고, 9시간이라면 1시간처럼 초과한 시간만 자동으로 계산됩니다.
=IF($F5="","",MAX(0,$F5-8))
만약 기본 근무시간이 8시간이 아닌 사업장이라면 수식 내의 8 부분만 원하는 기준 시간으로 변경해서 유연하게 사용할 수 있습니다.
월별 근무시간 자동 집계하기
매일매일 쌓인 데이터를 바탕으로 월말에 직원별 통계를 내야 하는 순간이 옵니다. 예를 들어 직원이 20명만 되어도 달력과 대조해가며 손으로 합계를 계산하는 데 시간이 꽤 걸립니다. 이때 많이 사용하는 함수가 바로 SUMIFS와 COUNTIFS이며, 이를 사용하면 직원 수가 아무리 늘어나도 반복 작업 없이 자동으로 집계됩니다.
- SUMIFS : 수많은 근태 데이터 중에서 '특정 직원 이름'과 '해당 월'에 맞는 행만 쏙 골라내어 총 근무시간이나 연장근무 시간의 합계를 구해줍니다.
- COUNTIFS : 이번 달에 직원이 출근한 일수, 지각한 횟수, 연차 사용 횟수 등을 조건에 맞춰 알아서 척척 카운트해 줍니다.
집계 연도와 월만 변경하면 직원별 근무시간과 출근일수, 지각 횟수, 휴가 횟수 등이 자동으로 집계됩니다.
이 두 함수를 함께 사용하면 직원별 근무시간은 물론 출근일수와 지각 횟수까지 자동으로 집계할 수 있어 월말 정산 시간을 크게 줄일 수 있습니다. 수식 구조가 처음에는 조금 낯설고 복잡해 보일 수 있어도, 집계 연도와 월만 변경하면 모든 직원의 한 달 근태 현황이 자동으로 집계되는 편리함을 맛볼 수 있습니다.
근무시간 계산 시 알아두면 좋은 점
근태관리 핵심 함수 역할 요약
이번 글에 등장한 주요 함수들이 시트 안에서 구체적으로 어떤 역할을 담당하는지 표로 간단히 정리해 보았습니다. 수식을 이해하는 데 참고해 보세요.
| 함수 | 역할 |
|---|---|
| IF | 조건에 따라 정상, 지각 등 다른 결과 표시 |
| OR | 휴가 중이거나 출퇴근 빈 칸 등 여러 조건 중 하나라도 만족하는지 확인 |
| MAX | 근무시간이 기준보다 적어 마이너스가 나올 때 음수 대신 0 반환 |
| TIME | 오전 9시 정각 등 비교 기준이 되는 시간을 만드는 함수 |
| SUMIFS | 특정 직원, 특정 월 등 조건에 맞는 근무시간 합계 계산 |
| COUNTIFS | 이번 달 지각 횟수, 출근 일수 등 조건에 맞는 개수 집계 |
계산 결과가 0.375처럼 표시되는 경우
구글시트와 엑셀이 시간을 저장하는 고유의 방식 때문입니다. 당황하지 마시고 계산식 뒤에 *24를 확실히 곱해주신 다음, 상단 메뉴에서 셀 서식을 [일반] 또는 [숫자]로 변경해 주시면 익숙한 시간 숫자로 돌아옵니다.
| 표시 결과 | 의미 |
|---|---|
| 0.375 | 9시간(24시간 기준 비율) |
| 9 | 실제 근무시간(시간 단위) |
따라서 근무시간을 계산할 때는 시간 차이를 구한 뒤 *24를 곱하면 우리가 익숙한 시간 단위 숫자로 사용할 수 있습니다.
휴게시간은 반드시 '소수점 숫자'로 입력하기
많은 분들이 실수하시는 부분인데요. 휴게시간을 입력할 때 30분을 00:30 처럼 시간 형식으로 넣으면 수식이 꼬여 엉뚱한 결과가 나옵니다. 30분은 0.5, 1시간은 1, 1시간 30분은 1.5와 같이 반드시 시간 단위의 숫자로 입력해야 정확하게 계산됩니다. 시간을 00:30 형식으로 입력하면 의도한 결과가 나오지 않을 수 있으니 주의해야 합니다.
빈 셀과 휴가 데이터는 무조건 먼저 걸러내기
수식을 짤 때 출퇴근 빈칸이나 휴가 여부를 고려하지 않으면 #VALUE! 같은 오류를 마주하게 됩니다. 언제나 IF와 OR 함수를 사용해 수식의 가장 앞단에서 예외적인 상황을 먼저 필터링해 주는 것이 에러 없는 깔끔한 시트를 만드는 핵심 노하우입니다.
엑셀에서도 동일하게 사용할 수 있습니다
오늘 구글 스프레드시트 기준으로 설명해 드린 IF, OR, MAX, TIME, SUMIFS, COUNTIFS 등의 함수들은 마이크로소프트 엑셀(Excel)에서도 완벽하게 동일한 문법으로 작동합니다. 어떤 프로그램을 쓰시든 그대로 적용해 보세요.
근태관리 양식을 바로 사용하고 싶다면
위에서 소개한 수식이 모두 적용된 근태관리 양식입니다. 구글시트는 내 드라이브로 사본을 만들어 바로 사용할 수 있고, 엑셀 파일도 함께 제공합니다.
