엑셀 함수와 수식 그리고 자동 계산 문서 서식과 관련한 글을 계속해서 발행하면서 느낀점으로 생각보다 근무시간, 급여관리와 같은 자동 계산 서식을 엑셀로 관리 하고 있는 분들이 많고 시간과 관련한 수식을 작성하는데 어려움을 느끼는 분들이 많은것 같습니다.
이전에 발행 했던 글 역시 이러한 시간과 관련해서 근무시간, 근로시간, 급여계산 자동 계산 서식을 작성하기 위해 필요한 수식들을 작성해 보았으니 참고해 보시길 바랍니다.
시간과 관련한 수식으로 특히나 출퇴근 시간의 데이터값으로 근무 시간을 계산하는 수식을 작성하는데 어려움이 따르는것은 사용자마다의 시간 적용 환경이 다르고 시간을 계산해야 하는 경우의수가 굉장히 다양하기 때문일것입니다.
이번 글에서는 사용자의 환경에 따라 다르게 계산식을 적용해야 하는 경우가 많지만 일반적으로 적용될 수 있는 경우들의 근무 시간을 계산하는 수식을 작성해 보도록 하겠습니다.
근무 시간 계산 수식.
근무 시간을 계산하기 위한 수식을 작성해 보기 위해서 위 이미지에서 보는것과 같이 출근 시간과 퇴근 시간에 따라 계산식을 작성해야 하는 여러 상황을 모두 계산할 수 있는 출퇴근시간을 다음과 같이 작성해 보았습니다.
- 출근시간 – 08:00 / 퇴근시간 – 17:00 / 점심시간 – 1시간
- 출근시간 – 08:00 / 퇴근시간 – 19:00 / 점심시간 – 1시간 / 저녁시간 – 1시간
- 출근시간 – 08:00 / 퇴근시간 – 23:00 / 점심시간 – 1시간 / 저녁시간 – 1시간
- 출근시간 – 21:00 / 퇴근시간 – 06:00 / 저녁시간 – 1시간
- 출근시간 – 08:00 / 퇴근시간 – 17:00 / 저녁시간 – 1시간
위와 같은 5가지 경우로 출퇴근시간이 발생 했을 경우 원하는 계산 결과값이 나올 수 있는 수식을 작성해야 합니다.
먼저 1번~5번까지 각각의 근무유형을 계산하는 기본적인 수식들을 작성해 보고 최종적으로 모든 유형에 적용될 수 있는 수식을 작성해 보면서 시간 계산과 관련해서 생각해 봐야할 조건들과 수식들을 익혀보시길 바랍니다.
1번의 경우에는 (퇴근시간 – 출근시간) – (점심시간)으로 총 근로시간은 8시간으로 기본 근로시간외에 추가 근무시간이 발생하지 않았기 때문에 =(C4-B4)*24-D4 또는 =((C4-B4)*1440)/60-D4과 같이 수식을 작성할 수 있습니다.
2번의 경우에는 점심시간과 저녁시간을 뺀 실제 총 근무시간의 합계는 9시간으로 연장근무 시간이 1시간이 발생했기 때문에 야간/연장 근무시간 1시간까지 계산할 수 있는 수식을 작성해 주어야 하기 때문에 기본 근무시간의 수식 =IF(((C5-B5)*1440)/60-D5-E5>=8,8,((C5-B5)*1440)/60-D5-E5), 야간/연장 근무시간의 수식은 총 근무시간 – 기본근무시간으로 계산해 주면 되기 때문에 =F5-G5으로 수식을 작성할 수 있습니다.
3번의 경우 기본근무시간, 야간/연장근무시간, 22시 이후의 심야근무시간이 모두 포함된 근무형태이기 때문에 모든 근무유형에 근무시간값이 계산될 수 있도록 수식을 작성해 주어야 합니다.
1번, 2번에서 작성한 수식을 총근무시간, 기본근무시간, 야간/연장근무시간에 작성을 해 주면 총근무시간 13시간, 기본근무시간 8시간, 야간/연장근무시간 5시간으로 결과값을 반환하는데, 22시이후부터는 심야 근무시간으로 적용되기 때문에 심야근무시간 야간/연장근무시간 4시간, 심야근무시간 1시간으로 결과값이 나오는 수식으로 작성해 주어야 합니다.
일반적인 근무형태에서는 심야 근무시간은 22시~익일06시까지로 가정했을때 심야 근무시간의 수식은 =(IF(C6<B6,MIN(TIME(6,0,0),C6)+1,MAX(TIME(22,0,0),C6))-MAX(B6,TIME(22,0,0)))*(1440/60)과 같이 작성해 주면 22시~23시까지의 심야 근무시간 1시간을 계산 할 수 있습니다.
따라서 야간/연장근무시간의 수식은 =F6-G6 → =F6-G6-I6으로 수식을 수정해주면, 야간/연장근무시간 4시간, 심야근무시간 1시간으로 계산한 결과값을 반환할 수 있습니다.
4번과 5번의 근무유형은 1번~3번까지 근무형태와 다르게 출근시간이 퇴근시간값보다 큰값이 입력되어 있기 때문에 퇴근시간-출근시간으로 수식을 작성해 주게 되면 이상한값이 나오게 됩니다.
엑셀에서 시간은 숫자형태로 변환하게 되면 0~1의값으로 변환되므로 최대값 1은 24시간을 의미하는것으로 21:00를 입력하고 표시형식을 일반으로 변경하게 되면 0.875의 값으로 확인할 수 있고, 24:00를 입력하고 표시형식을 일반으로 변경하게 되면 1의값을 확인할 수 있습니다.
따라서 하루는 24시간으로 시간결과값을 정수 형태로 반환하기 위해서 퇴근시간-출근시간에서 *24 또는 (*1440)/60을 해주었기 때문에 출근시간이 퇴근시간보다 클 경우에는 (퇴근시간-출근시간+1)*24 또는 (*1440)/60으로 작성하면 총 근무시간을 계산할 수 있습니다.
다양한 근무 유형을 모두 계산할 수 있는 수식.
위에서 1번부터 5번까지 출퇴근시간으로 근무 유형을 나눠서 근무 시간을 계산하기 위한 유형을 나눠봤는데, 모든 근무 형태의 시간을 계산하기 위해서는 IF함수로 계산 수식을 연결해 주면 됩니다.
IF함수로 계산 수식을 연결해 주는것을 IF 중첩 함수라고 합니다.
총근로시간, 기본근로시간, 야간/연장근로시간, 심야/특근근로시간의 수식을 IF중첩함수로 수식을 작성하면 다음과 같이 작성할 수 있습니다.
=(IF(B4<C4,(C4-B4)*1440, IF(OR(B4=””,C4=””),””, IF(B4>C4,(1+C4-B4)*1440)))/60)+SUM(-D4-E4) |
=((IF(B4<C4,(C4-B4)*1440, IF(OR(B4=””,C4=””),””, IF(B4>C4,(1+C4-B4)*1440)))/60)-D4-E4)-H4-I4 |
=IF(IF(OR(B4=””,C4=””),””, IF(AND(B4<C4,((C4-B4)*1440)/60-D4<=8),0, IF(AND(B4<C4,((C4-B4)*1440)/60>8),((C4-B4)*1440)/60-8-E4-D4, IF(B4>C4,((1+C4-B4)*1440)/60-8-D4-E4)))-I4)<0,0, IF(OR(B4=””,C4=””),””, IF(AND(B4<C4,((C4-B4)*1440)/60-D4<=8),0, IF(AND(B4<C4,((C4-B4)*1440)/60>8),((C4-B4)*1440)/60-8-E4-D4, IF(B4>C4,((1+C4-B4)*1440)/60-8-D4-E4)))-I4)) |
=(IF(OR(B4=””,C4=””),””, IF(C4<B4,MIN(TIME(6,0,0),C4)+1,MAX(TIME(22,0,0),C4))-MAX(B4,TIME(22,0,0))))*(1440/60) |
위 수식은 결과값이 나와야 하는 첫셀에 입력후 계산 결과값이 출력되어야 하는 셀까지 수식을 복사하여 사용할 수 있습니다.
글을 마치며…[행아아빠 생각]
시간과 관련한 함수와 수식으로 이번 글에서는 특히나 근무 시간을 계산 하는 수식으로 근무 시간을 계산하는 유형에는 사용자의 환경에 따라 조건이 달라지는 경우가 굉장이 다양할 수 있습니다.
따라서 일반적인 근무 형태에서 발생할 수 있는 조건들로만 수식을 작성해 보았기 때문에 해당 수식을 직접 적용하기 어려운 경우도 있을 수 있습니다.
개인적으로 이러한 수식을 작성한다고 하면 어느정도 함수를 활용하여 수식을 작성할 능력이 있기 때문에 계산 수식이 어떻게 작동하는지 알 수 있을것이라고 생각합니다.
다만 정수의 계산이 아닌 시간을 계산하는것에 있어서 헷갈릴 수 있는 부분이 있을 수 있는데, 엑셀에서 시간의 값은 0~1까지의 값으로 변환할 수 있다는것만 염두해 둔다면 기타 시간 계산 수식을 작성하는데에 도움이 될 수 있으리라 생각합니다.
이번 글을 통해서 여러분들의 업무 효율이 증대될 수 있기를 바랍니다.
샘플 서식 다운로드.
| 엑셀 근무시간 계산 수식 작성 샘플 서식 With행아아빠 |
