Excel [지식인 해결 시리즈] 006. 근무시간에서 특정 시간 구하기 (야간 근무 인정 시간)

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

 

01. 문제

 

 

문제를 정리하면, 출근 시간과 퇴근 시간을 00:00 ~ 23:59 형태로 표기하는 시간으로 입력 후에, 심야 시간에 해당하는 시간을 구해달라는 이야기로 보입니다. 

 

심야시간은 22:00 ~ 06:00 까지만 인정되며, 교대 근무의 형태에 따라 오후 타입에는 1 시간의 휴게시간 제외, 심야 타입에는 0.5시간의 휴게시간을 제외해야 합니다.

근무가 휴무이거나, 교대 형태가 주간인 경우에는 심야시간으로 인정이 안되네요.

 

근무일자까지 함께 표기하면 문제가 좀 더 쉬워지겠지만, 출근/퇴근을 24시간 현태의 시간으로 표기하고 있어서 문제가 좀 복잡할 것 같습니다.

 

 

02. 문제 해결 방법

 

불가능 한 것은 아니지만 한 셀 안에서의 수식으로 위의 문제를 다 해결하는 것은 굉장히 긴 IF문을 작성하게하는 문제가 있습니다. 이 포스트에서는 여러 셀로 나눠서 설명해보겠습니다.

 

1) 시간의 대소 관계

출근 퇴근을 입력한 셀의 셀서식을 시간으로 바꾸면, 엑셀에서는 해당 데이터를 시간으로 저장하게 됩니다.

 

이 상태에서는 대소관계를 확인하는 것이 가능하며, 시간과 분을 따로 추출할 수 있습니다.

셀 서식 지정

출퇴근 시간의 대소관계

 

출근 시간과 퇴근 시간의 대소관계를 확인하는 이유는, 퇴근 시간이 출근 시간보다 더 작은 값일 수도 있기 때문입니다. 출퇴근 일자를 표기 하지 않는 표기 방식의 한계라고 봐야죠.

 

2) 시간, 분 추출

Hour 함수와 Minute 함수로 시간데이터에서 시간(Hours) 와 분(minutes)를 추출하는 것이 가능합니다. 단 셀 서식이 시간이어야 합니다.

시간 추출

분 추출

 

3) 경우의 수 계산

 

근무형태가 휴무인 경우는 계산에서 제외하고, 교대형태가 주간인 경우도 계산에서 죄외합니다.

간단하계 =if(OR(B23="휴무", C23="주간",0, [메인로직] ) 으로 제외가 가능합니다

 

일단 휴게시간을 계산하기 전에 심야시간으로 인정받을 수 있는 시간 (22:00 ~ 06:00)과 겹치는 시간을 구해봅시다.

 

 * 출근 시간과 퇴근시간을 시간의 형태로 통합합시다.   (시간 + 분/60 으로 시간 단위로 통합이 가능합니다.)

 

시간으로 합산

 

 (1) 출근 시간 < 퇴근 시간인 경우

     이 경우는 하루를 넘어가지 않은 경우 입니다. 출근과 퇴근이 같은 날에 이루어진 경우로 다음과 같이 4가지 경우로 생각해볼 수 있습니다.

    -  6시 이후에 출근하여 22시 이전에 퇴근

       심야시간: 0시간

    -  6시 이전에 출근하여 22시 이전에 퇴근

       심야시간: 6-출근시간

    -  6시 이후에 출근하여 22시 이후에 퇴근

       심야시간: 퇴근시간 - 22

    -  6시 이전에 출근하여 22시 이후에 퇴근

       심야시간: 6-출근시간 + 퇴근시간 - 22

 

 (2) 출근 시간 > 퇴근 시간인 경우

      이 경우는 퇴근이 출근한 다음날 이뤄진 경우 입니다. 다음과 같이 3가지 경우로 생각해 볼 수 있습니다.

    - 출근시간이 6시 이전인 경우

      심야시간: 6-출근시간 + 2 + 퇴근시간

       * 2시간을 더하는 이유는 22~24시까지의 2시간, 0시에서 퇴근시간까지 모두 심야시간으로 볼 수 있음

    - 출근 시간이 6시 이후, 22시 이전인 경우

       심야시간: 2+MIN(6, 퇴근시간) 

       * 22~24시까지의 2시간과, 최대 6시간까지의 심야시간 인정,  MIN은 두 값 중 최소값을 의미

    - 출근 시간이 22시 이후인 경우

       심야시간: 출근시간 - 22 + MIN(6, 퇴근시간)

        *출근 시점부터 24시까지의 시간 + 최대 6시간까지의 심야시간 인정

 

  4) 교대형태에 따른 휴게 시간 차감

   - 교대형태가 주간인 경우 1를 차감하면 됩니다. 

     시간이 마이너스일 필요는 없으니  MAX(0, 심야시간 - 1)를 해줍시다.

  - 교대형태가 심야인 경우 0.5를 차감하면 됩니다.

     시간이 마이너스일 필요는 없으니  MAX(0, 심야시간 - 0.5)를 해줍시다.  

 

 

03. 예제 및 설명

 

1) 경우의 수를 나눠서 셀에 논리식을 채운다.

2) 논리식에 맞는 시간을 계산한다.

논리에서 벗어나는 경우 계산 시간을 0으로하고 일치하는 경우 시간을 계산한다. 

* 하나의 IF분으로 해보려고했는데, 너무 복잡해서 그냥 셀로 나눠서 해결해봤습니다.

필요없는 셀을 숨긴 모습