근무 시간을 직접 계산해 본 경험이 있다면 계산할 때 고려해야 할 사항이 많고 이래저래 복잡하다는 것을 알 것이다.
일단 시간들의 교집합을 찾는 방법으로 'Max(0, Min(종료, 퇴근) - Max(시작, 출근))' 정도만 기억하고 시작해 보자.
아래 그림에서 [참조1] 표는 일상적으로 사용하는 표 양식이다. 근무 시간은 언제부터 언제까지이고, 근무 시간에서 제외하는 시간은 언제부터 언제까지인지 기재한다. [참조1] 표를 참고해서 만든 수식은 '주간시간1'이다.
[참조1] 표는 이해는 쉽게 되지만 계산을 하는 데에는 불편한 양식이다. 이러한 양식을 그대로 둔 상태에서 수식을 작성한다면 아래와 같은 길고 복잡한 수식이 된다.
D17 셀(주간시간1, 배열수식으로 입력):
=IF(B17*C17>0, MIN($C$9, (B17>C17)+C17)-MAX($B$9, B17)-SUM((TRANSPOSE($B$10:$B$12)<=(ROW(INDIRECT(MAX($B$9, B17)*1440&":"&MIN($C$9, (B17>C17)+C17)*1440))/1440))*((ROW(INDIRECT(MAX($B$9, B17)*1440&":"&MIN($C$9, (B17>C17)+C17)*1440))/1440)<TRANSPOSE($C$10:$C$12)))/1440, 0)
그래서 휴게 시간은 제외하고 근무 시간에 반영할 시간대만 골라 정리한 [참조2] 표를 만든 다음, 이를 참고한 수식이 '주간시간2'이다.
E17 셀(주간시간2):
=IF(B17*C17>0, SUM(MAX(0, MIN($H$9, (B17>C17)+C17)-MAX($G$9, B17)), MAX(0, MIN($H$10, (B17>C17)+C17)-MAX($G$10, B17)), MAX(0, MIN($H$11, (B17>C17)+C17)-MAX($G$11, B17)), MAX(0, MIN($H$12, (B17>C17)+C17)-MAX($G$12, B17))), 0)
이 수식은 다시 아래처럼 바꾸면 좀 더 간단해진다.
E17 셀(주간시간2, 배열수식으로 입력):
=IF(B17*C17>0, SUM(IF(IF($H$9:$H$12>=((B17>C17)+C17), (B17>C17)+C17, $H$9:$H$12)>IF($G$9:$G$12>=B17, $G$9:$G$12, B17), IF($H$9:$H$12>=((B17>C17)+C17), (B17>C17)+C17, $H$9:$H$12)-IF($G$9:$G$12>=B17, $G$9:$G$12, B17), 0)), 0)
예제에서는 야간시간을 따로 나누었는데 필요 없다면 위의 수식에서 범위만 바꿔주면 된다.
F17 셀(야간시간):
=IF(B17*C17>0, MAX(0, MIN($H$13, (B17>C17)+C17)-MAX($G$13, B17)), 0)
참고로 수식 중에서 배열수식은 CTRL + SHIFT + ENTER 키를 함께 눌러야 제대로 된 결과가 나온다.