엑셀에서 출퇴근 시간으로 총 근무시간 계산 방법(정상, 연장(잔업) 근무시간)

엑셀에서 출근과 퇴근 시간이 주어졌을 때 근무 시간을 계산하려면 어떻게 하면 될까요?

총 근무시간이라고 하면 정상 근무시간과 연장(잔업,야간) 근무시간을 모두 합한 시간을 의미합니다.

근무시간을 계산할 때 점심 시간과 저녁 식사 시간을 빼주어야 함으로 단순하게 퇴근 시간에서 출근 시간을 뺀다고 해서 근무시간이 계산되는 것은 아닙니다.

그리고 정상 근무시간과 연장(잔업) 근무시간을 별도로 구분해서 계산도 해 주어야 합니다.

이번 글에서는 엑셀에서 출퇴근 시간으로 총 근무시간 계산 방법(정상, 연장(잔업) 근무시간)에 대해 알아보겠습니다.

엑셀 정상근무시간, 연장근무시간, 총근무시간 계산 방법

▼ 먼저 엑셀에서 근무 시간을 계산한 화면을 먼저 보도록 하겠습니다.

1. 근무 시간 계산을 위한 기본 정보는 아래와 같습니다.

• 정상 근무시간은 08:30분부터 17:30분까지 입니다.
• 중간에 12:00부터 13:00까지 점심 시간이 있습니다.
• 17:30분부터 18:00까지 저녁 시간입니다.
• 연장 근무는 18:00부터 시작됩니다.

2. 정상 근무 시간을 계산해 보겠습니다.

• E2 셀의 정상 근무 계산 식은 =IF(C2<=0,0,IF(AND(C2<=TIME(8,30,0),D2>=TIME(17,30,0)),TIME(17,30,0)-TIME(8,30,0),D2-TIME(8,30,0))-TIME(1,0,0)) 입니다.
• C2<=0 : 출근 시간이 없으면 정상 근무시간은 0 입니다.
• AND(C2<=TIME(8,30,0),D2>=TIME(17,30,0)) : 출근 시간이 08:30 이전이고, 퇴근 시간이 17:30 이후일 때
• TIME(17,30,0)-TIME(8,30,0) : 17:00분에서 08:30분을 빼 줍니다. 결과적으로 09:00이 됩니다.
  TIME(17,30,0)-TIME(8,30,0) 계산 식 대신 TIME(9,0,0)으로 직접 시간 값을 입력해도 됩니다.
• D7-TIME(8,30,0) : 만약 퇴근 시간이 17:30 이전이라면 퇴근 시간에서 08:30분을 빼 줍니다.
• -TIME(1,0,0) : 점심 시간 1시간을 빼 줍니다.

3. 연장 근무 시간을 계산해 보겠습니다.(잔업 시간)

• F2 셀의 연장 근무 계산 식은 =IF(D2>TIME(18,0,0),D2-TIME(18,0,0),0) 입니다.
• D2>TIME(18,0,0),D2-TIME(18,0,0) : 퇴근 시간이 18:00 이후인 경우 퇴근 시간에서 18:00분을 빼 줍니다.
• 만약 퇴근 시간이 18:00 이전이라면 연장 근무 시간은 0 입니다.

4. 총 근무 시간을 계산해 보겠습니다.

• G2 셀의 총 근무시간 계산 식은 =FLOOR(E2,TIME(0,10,0))+FLOOR(F2,TIME(0,10,0)) 입니다.
• 10분 단위로 계산하기 위해 FLOOR 함수를 사용하였습니다.
• FLOOR 함수를 사용하지 않고, 단순히 =E2+F2 수식으로 계산하면 G2셀 값은 9:05이 됩니다.

추가로 검토해야 할 사항들

• 야간 근무나 특근 시간을 추가로 반영해야 할 경우가 있습니다.
• 평일과 휴일(공휴일)에 대한 근무 시간이 다르게 적용될 수 있습니다.
• 점심 시간 출퇴근(오전/오후 반차) 또는 지각,조퇴에 대한 추가 수식을 고려해야 할 경우가 있습니다.

마무리

가장 기본적이면서 정상적인 출퇴근 시간에 대한 근무 시간을 간단하게 계산하는 방법에 대해 알아보았습니다.

근무 시간을 계산하기에 앞서 시간을 10분 단위, 30분 단위로 반올림, 올림, 버림을 먼저 한 상태에서 계산을 해주어야 할 때도 있습니다. 특정 분 단위로 시간을 반올림, 올림, 버림 하는 방법은 엑셀에서 시간을 30분 단위, 10분 단위, 5분 단위로 반올림, 올림, 버림 수식 방법 글을 참고하시면 됩니다.

아래 도움이 될 만한 다른 포스팅 글도 참고해 보세요.

♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.