근무시간 집계하기: 출퇴근 기록으로 주간 근무시간과 연장근로를 계산할 수 있나요? | 클로드 엑셀왕 9

이 장에서 다루는 것

엑셀왕 TIP 합치기 전에 원본 숫자 적어 두기

단말기 파일에서 출근 줄 수와 퇴근 줄 수를 세어 둡니다. 필터로 구분 열을 고르면 상태 표시줄에 개수가 나옵니다. 출근과 퇴근 수가 다르면 어딘가에 짝 없는 줄이 있다는 뜻입니다.

이 장의 예제에서는 출근 41줄, 퇴근 40줄, 수기입력 3줄입니다.

클로드에서는 출입기록과 직원명부를 한 대화에

클로드 앱(웹·데스크톱·모바일)의 대화창에 출입기록 파일과 직원명부(예정 출근 시각)를 함께 첨부하고 시작합니다. 지각을 표시하려면 예정 출근 시각이 필요합니다.

근태 기록은 개인정보입니다. 주민등록번호·연락처·주소가 든 열은 지우고, 회사의 개인정보 처리 규정이 외부 도구 사용을 허용하는지 먼저 확인하세요.

이렇게 말하기

출입 단말기 기록이야. 직원별로 출근·퇴근을 묶어서 주간 근무시간을 내기 전에, 기록에서 이상한 곳과 내가 정해야 할 기준을 먼저 보여 줘.

그러면 처리되는 일

· 출근·퇴근 줄 수를 세고, 짝이 안 맞는 곳을 행 번호로 짚습니다.

· 자정을 넘긴 퇴근, 글자로 된 일시, 퇴근 누락을 따로 표시하고, 휴게·반올림·지각·연장 기준을 묻습니다.

이렇게 답하기

휴게는 실제 기록이 없으니 머문 시간이 8시간 30분 이상이면 1시간, 4시간 이상이면 30분을 빼 줘. 반올림하지 말고 분 단위 그대로, 지각은 예정 출근보다 늦은 분만 표시해 줘. 연장은 하루 8시간 넘은 시간에, 그걸 뺀 주 근무시간이 40시간을 넘은 시간을 더해 줘. 한 주는 월요일부터 일요일까지. 이준호 10월 15일 퇴근은 매니저 확인으로 20:05야.

그러면 처리되는 일

· 통합 파일에 ‘기준’ 시트를 만들어 정한 기준을 적어 둡니다.

· 이준호 10월 15일 줄의 ‘퇴근 보정(사람 입력)’ 칸에 20:05를 넣고 노란색으로 칠해, 원본에서 온 값과 구별합니다.

엑셀왕 노트 법 기준은 따로 확인

근로기준법은 휴게시간을 빼고 1주 40시간, 1일 8시간을 기준 근로시간으로 정하고, 근로시간이 4시간이면 30분 이상, 8시간이면 1시간 이상의 휴게를 근로시간 도중에 주도록 정합니다(제50조·제54조, 고용노동부 안내 기준). 상시 근로자 5명 이상 사업장에 적용되는 조항입니다.

위의 휴게 차감은 실제 휴게 기록이 없을 때 쓰는 예제 가게의 규정입니다. 실제로 쉬지 못한 날이 있는지, 연장근로 한도와 가산수당을 어떻게 적용할지는 회사 규정과 노무 전문가에게 확인하세요. 반올림처럼 근무시간을 깎을 수 있는 처리도 마찬가지입니다.

이렇게 말하기

정한 기준대로 출근 하나에 퇴근 하나를 짝지어 근무기록 시트를 만들어서 엑셀 파일로 줘. 출근·퇴근이 몇 번째 원본 행에서 왔는지 남기고, 머문 시간·휴게·근무시간은 수식으로 계산해 줘.

그러면 처리되는 일

· 같은 사람의 출근 다음에 오는 퇴근을 짝으로 묶어 근무 41건을 만듭니다. 날짜가 바뀌어도 앞의 출근에 붙입니다.

· 출입기록 시트에 ‘일시(정리)’ 열을 더해 글자로 된 일시를 날짜·시각 값으로 바꾸고, 근무기록은 원본 행 번호로 이 값을 가져옵니다.

· 머문 시간 = 퇴근 − 출근, 근무시간 = 머문 시간 − 휴게로 계산하고 서식은 [h]:mm, 옆 열에 ×24 한 시간 숫자를 둡니다.

수식 한 줄 읽기 화면 9-4의 K4 셀

=IF(J4*1440>=510, 1/24, IF(J4*1440>=240, 1/48, 0))

· J4*1440 → 머문 시간(하루 단위)에 1,440을 곱해 분으로 바꿉니다. 박서연 10월 5일은 7:30이라 450분입니다.

· >=510 → 510분(8시간 30분) 이상이면 1/24, 곧 1시간을 뺍니다.

· >=240 → 240분(4시간) 이상이면 1/48, 곧 30분을 뺍니다. 450분은 여기에 걸려 휴게 0:30입니다.

한 줄로 읽으면 “머문 시간을 분으로 바꿔 규정표에 맞는 휴게를 고른다”입니다. 1/24와 1/48처럼 시간을 하루의 몇 분의 일로 쓰는 것이 엑셀 시간 계산의 기본입니다.

이렇게 말하기

출입기록 원본의 출근·퇴근 수와 근무기록 행 수가 맞는지, 근무시간이 0 이하이거나 너무 긴 행은 없는지, 근무기록 합계와 주간집계 합계가 같은지 보는 검산 시트를 만들어 줘.

그러면 처리되는 일

· 출근 41줄 = 근무 41건, 퇴근 40줄 + 보정 1 = 근무 41건인지 봅니다.

· 짝을 잘못 묶으면 근무시간이 음수가 되거나 하루를 넘깁니다. 0 이하인 행과 머문 시간이 16시간을 넘는 행을 셉니다.

· 모두 맞으면 ‘일치’가 나오게 합니다.

클로드에서는 받은 파일은 Claude for Excel로 점검

받은 파일을 엑셀에서 열고 Claude for Excel 창에 “주간집계 G11은 어떻게 나온 숫자야?”라고 물으면, 근무기록 시트의 어느 칸들을 더했는지 셀 위치를 짚어 설명합니다. 답에 나온 셀 위치를 누르면 그 칸으로 바로 갑니다.

숫자 하나가 이상해 보일 때 수식을 직접 따라가는 대신 이렇게 묻고, 짚어 준 칸을 눈으로 확인하면 됩니다. Claude for Excel을 쓸 수 없는 환경이면 대화창에서 “이 파일의 주간집계 G11 수식을 풀어서 설명해 줘”라고 물어도 됩니다.

이렇게 말하기

직원별·주차별로 근무일수, 근무시간([h]:mm과 시간 숫자), 하루 8시간 넘은 시간, 주 40시간 넘은 시간, 연장 합계, 지각 횟수를 요약해 줘.

그러면 처리되는 일

· 주간집계 시트에 SUMIFS로 근무기록을 이름·주차별로 더합니다.

· 주 40시간 넘은 시간은 주 근무시간에서 하루 8시간 넘은 시간을 뺀 뒤 40을 넘은 만큼으로 계산합니다. 같은 시간을 두 번 세지 않으려는 식입니다.

클로드에서는 기준을 프로젝트에 두기

매주 같은 집계를 한다면 클로드 프로젝트를 하나 만들고, 완성 파일의 기준 시트를 프로젝트 지식에 올려 두세요. 프로젝트 지침에는 “출입기록은 기준 시트대로 집계한다. 퇴근 누락은 채우지 말고 묻는다. 원본 출근·퇴근 수로 검산한다. 처음 보는 구분 값이 나오면 계산 전에 묻는다”라고 적습니다.

그러면 다음 주에는 그 프로젝트에서 새 기록을 첨부하고 “이번 주도 같은 방식으로”라고만 해도 됩니다.

한 번에 말하는 요청

출입 단말기 기록과 직원명부야. 계산하기 전에 자정 넘은 퇴근, 글자로 된 일시, 퇴근 누락 같은 이상한 곳과 휴게·반올림·연장 기준을 먼저 보여 줘.
내가 기준을 정하면 출근 하나에 퇴근 하나를 짝지은 근무기록 시트를 만들고, 원본 행 번호를 남겨 줘. 근무시간은 [h]:mm 서식과 ×24 한 시간 숫자로 함께 보여 줘.
원본 출근·퇴근 수와 근무 행 수를 비교하는 검산 시트, 직원별·주차별 하루·주 연장 요약 시트, 정한 기준을 적은 시트도 넣어서 엑셀 파일로 줘.

이 장의 핵심

엑셀은 시간을 ‘하루 = 1’로 센다. 24시간 넘는 합계는 [h]:mm 서식으로 보고, 시급을 곱할 때는 ×24 한 숫자를 쓴다.

계산하기 전에 자정 넘김·글자 일시·퇴근 누락을 먼저 짚게 하고, 휴게·반올림·연장 기준은 사람이 정한다. 누락된 시각은 사람이 확인해 넣는다.

원본 출근·퇴근 수와 근무 행 수를 맞춘 뒤에 주간 연장을 본다. 법 기준과 수당은 회사 규정과 노무 전문가에게 확인한다.

답 예시

외출부터 복귀까지는 휴게로 보고 빼 줘. 외출한 날은 규정 휴게 대신 실제 외출 시간을 빼고, 외출 시간이 규정 휴게보다 짧으면 규정 휴게를 빼 줘. 기준 시트의 휴게 칸도 고쳐 줘.

이렇게 답하면 19일은 머문 시간 7:32에서 외출 45분을 빼 근무시간이 6:47입니다. 규정대로 30분만 뺐다면 7:02이 나왔을 날입니다. 외출이 없는 21일은 머문 시간 7:35에서 30분을 빼 7:05이고, 두 날을 더하면 13:52입니다. 외출을 근무로 볼지 휴게로 볼지는 가게 규정으로 사람이 정하고, 기준 시트에 남겨 둡니다.