안녕하세요.
행아아빠입니다.
엑셀로 근무시간 또는 근태 등의 시간관리는 하시는분들이 꾀나 있으신 것 같습니다.
이에 따라 엑셀로 시간을 관리하다 보니, 오버타임, 초과근로시간, 야근시간 등을 질문 하시는분들도 종종 계시는데요.
몇가지 질문별로 시간 계산을 설명드려 보려고 합니다.
수식은 조금씩 다르게 적용될 수 있겠지만, 결국 구하고자 하는 값은 기본근무시간을 제외한 초과근로시간이 될 것 입니다.
■ 질문 유형1
엑셀을 잘 몰라서요.
퇴근시간은 19:00시입니다 . 7시 이후에 일한시간은 산출해내고싶은데
수식을 어떻게 걸어야할까요?
위 질문 주신 내용은
굉장히 간단하게 수식을 작성할 수 있습니다.
기본적으로 참조할 수 있는 정규퇴근시간이란 열에 19:00를 모두 입력해 놓았기 때문이죠!!
오버타임은 다시 말해서 정규퇴근시간보다 추가로 근무한 시간을 구하고자 하시는 것일텐데요.
그렇다면, 당연히 퇴근시간 - 정규퇴근시간을 해주시면 추가로 근무한 시간인 오버타임을 구할 수 있겠습니다.
이때, 조건은 퇴근시간이 정규퇴근시간에 비해서 빨리 퇴근하거나,
정규퇴근시간에 맞춰서 퇴근했을때에는 오버타임이 0일 것이고,
정규시간보다 퇴근시간이 늦을때에는 퇴근시간 - 정규시간을 구하는 것으로 수식작성을 완료 시킬 수 있겠습니다.
▲ =IF(D2<E2,"0",D2-E2)
작성한 수식 굉장히 간단명료하죠~??!!
■ 질문유형2
▲ AM: 1:00 에 출근하여 AM 10:00에 퇴근할시
야간근로시간적용(22:00 ~ 06:00) 과 휴게시간 적용 (12:00 ~ 13:00) 하여
연장근무시간과 일반근로시간을 나눠서 자동계산이 되는 엑셀서식을 구하고싶습니다.
위 질문 내용에서 참고해야할 조건으로는
야간근로시간적용은 (22:00~06:00)와 휴게시간은 (12:00~13:00) 두가지를 염두해 두어야 할 것 같습니다.
야간근로시간 적용시간을 제시해 주셨기 때문에, 사실은 시급*2배인 특근시간도 별도로 구분을 해 주어야할 것 같지만,
▲ 질문 내용으로 봐서는
일반근무시간은 총근로시간, 연장시간은 22:00~06:00 사이에 근무한 시간을 구하고자 하는것으로 이해하고 수식을 작성하였습니다.
그림에서 보는것처럼
일반 시간의 수식작성은
=IF(OR($D5="",$E5=""),"",
IF($E5>$D5,(SUM($E5-$D5)*1440)/60,
IF($D5>$E5,(SUM($D5-$E5)*1440)/60))) 이렇게 작성 할 수 있겠습니다.
▲ 연장시간인 22:00~06:00 사이 시간에 근무한 시간을 계산하는 수식은
=IFERROR((IF(OR($D5="",$E5=""),"",
IF($E5<$D5,MIN(TIME(6,0,0),$E5)+1,MAX(TIME(22,0,0),$E5))-MAX($D5,TIME(22,0,0))))*(1440/60),"")
이렇게 작성하시면 되겠습니다.
■ 질문유형3
상기 표에서
18:00 이후 근무시간은 연장근무입니다.
표에서 보시듯 00:00 익일로 근무가 넘어가게되어도 연장근무 입니다.
08:00~18:00 정상근무
질문해주신분께서 결론적으로 계산하고자 하는 값은
18:00 ~ 익일 08:00의 사이에 근무한 시간을 모두 초과근무시간으로 구하고자 하는 것으로 보입니다.
▲ 해당 조건으로 수식을 작성하여 적용해 보면,
=IF((IF(OR(A3="",B3=""),"",
IF(B3<A3,MIN(TIME(8,0,0),B3)+1,MAX(TIME(18,0,0),B3))-MAX(A3,TIME(22,0,0))))*(1440/60)<=0,"",
(IF(OR(A3="",B3=""),"",IF(B3<A3,MIN(TIME(8,0,0),B3)+1,MAX(TIME(18,0,0),B3))-MAX(A3,TIME(22,0,0))))*(1440/60))
이렇게 작성하시면 되겠습니다.
질문유형2번과 유사한 질문으로 초과근로시간을 22:00~익일 06:00 사이에 근무한 시간을 구하는 것인지,
18:00 ~ 익일 08:00 사이에 근무한 시간을 구하는 것인지로 구분하는 정도일 것 같습니다.
이상으로 초과근로시간을 계산하는 수식방법을
질문 유형에 따라 케이스별로 확인해 보았습니다.
어떻게 데이터를 작성하고 있는지에 따라, 또는 회사에서 초과근로시간을 어떻게 적용하고 있는지에 따라 수식은 조금씩 변하지만,
큰 틀은 변하지 않습니다.
그렇기 때문에 제가 작성해 드린 근로시간을 계산하는 방법에 대해 작성해 놓은 글을 링크 걸어놓으니,
확인하시면 도움이 되실 것 같습니다.
급여관리 - 근로시간 / 근무시간 자동 계산하는 수식 작성 방법.
날짜 자동 입력하는 수식 작성 방법.
특정 날짜의 요일과 몇 주차에 해당하는지 알 수 있는 수식 작성 방법.
급여시간별, 요일별에 따라서 실제 급여산출계산 시간을 적용할 수 있는 수식 작성 방법.
주휴수당 자동 계산하는 수식 작성 방법.
근로소득세 자동 계산하는 수식 작성 방법.
▲ 위 6가지 링크 걸어드린 엑셀로 급여관리 하는 방법들에 대해 확인해 보시면,
엑셀로 급여관리하시는 분들!!
이제까지 수기로 급여관리 하셨던분들, 별도의 프로그램이나 전산을 사용할 필요없이 급여관리하시는데 도움이 되시리라 생각됩니다.
그럼 오늘 내용도 많은 분들께 도움이 되시길 바라면서~
좋은 하루 보내세요~^^!
'엑셀' 카테고리의 다른 글
| [엑셀] 중복값 제외하고 불러온 결과값을 오른차순, 내림차순으로 정렬할 수 있는 수식 작성 방법. (0) | 2020.03.14 |
|---|---|
| [엑셀] VLOOKUP 함수를 쓰고 싶은데, 적용이 안되는 예제와 해결할 수 있는 함수와 수식 작성 방법. (0) | 2020.03.14 |
| [엑셀] If함수의 활용 - 시간계산 논리값 함수 작성 하는 방법. (0) | 2020.03.14 |
| [엑셀] 찾을값의 조건이 여러개인 VLOOKUP 함수?? - LOOKUP 함수로 대체해서 사용하세요. (5) | 2020.03.14 |
| [엑셀] 연차발생 수식 작성 방법 - If함수와 Countifs함수 응용. (0) | 2020.03.14 |
댓글