엑셀로 휴식시간 식사시간 제외한 근무시간 구하기

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

 

01. 문제

Q. 휴식, 식사 시간이 다음과 같이 주어져 있을 때, 휴식/식사 시간을 제외한 근무시간만 구하는 방법을 알려주세요.

 

 

문제는 출근시간, 퇴근시간을 입력하면, 휴시/식사 시간을 제외한 순수 작업시간을 구해달라는 것으로  보인다.

 

4번의 휴식과, 4번의 식사(조식, 중식, 석식, 야식) 시간을 모두 계산해야되므로 수식이 복잡해질 것 같다.

 

02. 문제 해결 방법

(1) 시간의 뺄샘을 이용한 시간 차이 구하기

엑셀에서 시간은 소수점으로 저장된다. 따라서 빼기를 이용하여 시간 차를 구하는 것이 가능하다.

뺄샘을 하면 소수점으로 값이 나올 수도 있는데 셀서식의 표기형식을 시간으로 변경하면 시간값을 구하는 것이 가능하다.

 

위의 그림처럼 시간값을 빼주면 두 시각 사이의 시간을 구할 수 있다.

 

(2) 시간 구간의 경우의 수

휴식/식사 시간은 결국 작업시간으로 쳐주지 않는 공제해야 되는 시간이다. 편의상 작업자가 작업장에 들어오는 시작시간, 작업장에서 나가는 시간을 종료시간으로 하자. 작업자의 종료시간 - 시작시간을 체류시간으로 하자.

 

체류시간 중에서 휴식시간, 식사시간을 공제하면 작업시간이 계산된다.

 

휴식시간/식사시간과 같은 공제해야되는 시간들을 편의상 공제시간이라고 하자.

 

 

하나의 공제 시간과 체류시간 사이에는 다음과 같이 6가지 경우의 수로 배치가 가능하다.

 

 - Case1: 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 < 공제시간 시작 인 경우

겹치는 시간이 없는 경우로, 모든 체류시간이 작업시간으로 인정 받는다. 

 

 - Case2: 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 < 공제시간 종료 인 경우

  일부 구간이 겹치는 경우로 공제시간 시작 시점부터 체류시간 종료까지 공제된다.

 

 - Case3: 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 > 공제시간 종료 인 경우

체류시간이 공제시간을 모두 포함하는 경우로 공제시간 전부가 공제된다.

 

 - Case4: 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 > 공제시간 종료 인 경우

겹치는 시간이 없는 경우로, 모든 체류시간이 작업시간으로 인정 받는다. 

 

 - Case5: 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료 AND 체류시간 종료 < 공제시간 종료인 경우

체류시간 전부가 공제시간안에 들어가 있기 때문에 체류시간 전체가 공제된다.

 

 - Case6: 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료  AND 체류시간 종료 > 공제시간 종료 인 경우

 

 

겹치는 시간이 있는 경우로 체류시간 시작부터 공제시간 종료까지 공제된다.

 

 

(3) 시간 구간의 경우의 수 중에서 공제시간을 따져야되는 조건

 

공제 시간 이 발생하는 경우는 총 4개의 경우 이다.  하나씩 조건으로 살펴보자

 

① 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 < 공제시간 종료

    실제로 공제되는 시간 = 체류시간 종료 - 공제시간 시작 

② 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 > 공제시간 종료

    실제로 공제되는 시간 = 공제시간 종료 - 공제시간 시작 

③ 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료 AND 체류시간 종료 < 공제시간 종료

    실제로 공제되는 시간 = 체류시간 종료 - 체류시간 시작 

④ 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료 AND 체류시간 종료 > 공제시간 종료

    실제로 공제되는 시간 = 공제시간 종료 - 체류시간 시작 

 

 

03. 예제

위에서 정리한 것을 엑셀 수식으로 표현해보자.

(1) 데이터 구성

위의 그림처럼 공제해야되는 휴식시간 점심시간을 시작시간과 종료시간으로 나열한다.

작업자1에 대해서 체류시간의 시작시간 종료시간을 E2, E3에 표기한다.

체류시간은 E3-E2로 구할 수 있다.

공제시간은 각각의 공제시간에서 02. 문제해결방법에서 도출한 수식을 사용하여 구할 예정이다.

작업시간은 체류시간에서 공제시간을 빼면 구할 수 있다.

 

(2) 공제조건 채우기

공제조건은 위에서 구한 4가지 공제 조건에 해당할 경우 TRUE를 아닐 경우 FALSE를 반환한다.

여러 조건이 AND로 묶여 있기 때문에 AND함수를 사용한다.

 

- 공제조건1

① 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 < 공제시간 종료

    실제로 공제되는 시간 = 체류시간 종료 - 공제시간 시작 

=IF(AND($E$2<=$B4,$E$3>=$B4,$E$3<=$C4),$E$3-$B4,0)

- 공제조건2

② 체류시간 시작 < 공제시간 시작 AND 체류시간 종료 > 공제시간 시작 AND 체류시간 종료 > 공제시간 종료

    실제로 공제되는 시간 = 공제시간 종료 - 공제시간 시작 

=IF(AND($E$2<=$B4,$E$3>=$B4,$E$3>=$C4),$C4-$B4,0)

- 공제조건3

③ 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료 AND 체류시간 종료 < 공제시간 종료

    실제로 공제되는 시간 = 체류시간 종료 - 체류시간 시작 

=IF(AND($E$2>=$B4,$E$2<=$C4,$E$3<=$C4),$E$3-$E$2,0)

 

 

- 공제조건4

④ 체류시간 시작 > 공제시간 시작 AND 체류시간 시작 < 공제시간 종료 AND 체류시간 종료 > 공제시간 종료

    실제로 공제되는 시간 = 공제시간 종료 - 체류시간 시작 

=IF(AND($E$2>=$B4,$E$2<=$C4,$E$3>=$C4),$C4-$E$2,0)

 

(3) 테스트하기

 - 체류시간을 10:00 ~ 10:35로 입력해보자.

휴식시간과 5분이 겹치는데, 5분이 제대로 계산되는 것을 확인할 수 있다.

 

  - 체류시간을 10:00 ~ 10:50로 입력해보자.

휴식시간 10분이 공제되는 것을 확인할 수 있다.

 

 - 체류시간을 10:32 ~ 10:38로 입력해보자.

체류시간 전체가 공제되는 것을 확인할 수 있다.

 

 - 체류시간을 10:32 ~ 10:50으로 입력해보자.

휴식 시간과 겹치는 시간 8분이 정상적으로 계산되는 것을 확인할 수 있다.

 

(4) 수식 복사하기

절대주소를 적절하게 사용하여 바로 복사할 경우 정상 동작한다. 복사하여 모든 공제시간 유형에 대한 실제 공제시간을 구해보자.

 

(5) 모든 공제 시간 합치기

E열에 모든 공제시간을 합치자

체류시간은 E3-E2로 구할 수 있다.

공제시간은 SUM(E4:E11)로 구할 수 있다.

작업시간은 체류시간에서 공제시간을 빼서 구할 수 있다.

 

위의 예제의 경우 10:00에 작업장에 도착해서 22:00에 작업장을 벗어난 경우, 휴식, 점심, 휴식, 석식 총 1시간 50분이 공제되어 12시간의 체류시간 중에서 10시간 10분의 작업시간이 인정된 케이스이다.