사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 근무시간에서 점심시간 빼고 계산하기

 

 

 

시간제로 근무하는 비정규직 직원의 업무시간을 계산할 때는 출/퇴근 시간을 확인해 점심시간을 빼고 계산한다. 점심시간을 포함한 근무시간에서 점심시간을 뺀 실제 업무시간을 계산하는 수식 작성방법에 대해 알아보자.

 

 

 

TIME 함수

TIME 함수는 시, 분, 초의 값을 받아서 시간을 의미하는 소수 값을변환하는 함수로 구문은 다음과 같다.

 

 

=TIME(시,분,초)

 

 

시 : 0~32,767 사이의 숫자 값으로 24보다 작은 시로, 큰 값은 24로 나눈 나머지 값을 시로 처리한다.

분 : 0~32,767 사이의 숫자 값으로 60보다 작은 분으로, 큰 값은 60으로 나눈 나머지 값을 분으로 처리한다.

초 : 0~32,767 사이의 숫자 값으로 69보다 작은 초로, 큰 값은 60으로 나눈 나머지 값을 초로 처리한다.

 

 

직원명부.xlsx

0.01MB

 

 

(1) 첨부파일을 다운로드하여 불러오기를 한다.

 

 

 

 

(2) 상기의 화면에서 보면 [J5:J24] 범위에 출근시간을 [K5:K24] 범위에 퇴근시간을 확인할 수 있다. 점심시간 12:00~13:00까지를 뺀 업무시간을 [L5:L24] 범위에 구해보도록 한다.

 

 

 

(3) 먼저 [L5] 셀에 [=K5-J5-IF(AND(J5<TIME(12,0,0),K5>TIME(13,0,0)),TIME(1,0,0),0)] 수식을 입력하고 [ENTER KEY]를 누른다. [L5] 셀의 채우기 핸들을 [L24] 셀까지 드래그해서 수식을 복사한다. 만약 근무시간에 AM 또는 PM이라는 오전, 오후 표기가 나타나면 [CTRL + 1], 또는 마우스 오른쪽 버튼 [셀 서식(F)]를 누르고, [표시형식] - [사용자지정]에서 [hh:mm]형식으로 변경한다.

 

 

=K5-J5-IF(AND(J5<TIME(12,0,0),K5>TIME(13,0,0)),TIME(1,0,0),0) 이해하기

 

 

점심시간이 근무시간에 포함되어 있는지 확인하려면 다음 두 가지 조건을 만족해야 한다.

 

① 출근시간이 점심시간이 시작되는 시간 전이어야 한다.

② 퇴근시간이 점심시간이 끝난 시간 이후여야 한다.

 

이 두 가지 조건을 처리하기 위해서 IF와 AND함수를 사용해 다음 조건을 완성한다.

 

 

IF(AND(J5<TIME(12,0,0),K5>TIME(13,0,0)),만족할때,만족하지않을때)

 

 

조건이 만족되면 퇴근시간(K5)에서 출근시간(J5)을 뺀 다음 1시간(TIME(1,0,0)을 추가로 빼준다. 이때 사용한 TIME(1,0,0)은 시간으로 보면 오전 1시를 가리키는 값이지만, 수식에서 사용될 때는 주로 시간의 간격을 의미하는 1시간이란 의미로 사용된다. TIME함수를 사용하지 않고 표시하려면 1/24 계산식을 사용할 수도 있다. 점심시간이 포함되지 않은 경우에는 퇴근시간에서 출근시간만 배주면 되므로 0을 반환한다.