엑셀로 근무 시간 계산하는 방법 이것만 알면 됨

근무 시간 계산을 어떻게 했는지 떠오르지 않아 예전 글 찾아보다

좀 더 간편한 방법이 떠올라 개정판(?)을 올린다.

 

근무 시간을 직접 계산해본 경험이 있다면

계산에 고려사항이 많고 이래저래 복잡하다는 것을 알 텐데...

일단 시간들의 교집합을 찾는 방법으로

Max(0, Min(종료, 퇴근) - Max(시작, 출근))

정도만 기억하고 시작하자.

 

 

참조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)

 

참조1 표는 이해는 쉽게 되는데 계산에는 불편한 양식이다.

그래서 휴게 시간은 제하고,

근무 시간에 반영할 시간대만 골라 정리한 참조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 키를 동시에 눌러야 정상적인 결과가 나온다.