엑셀(Excel) 요일별, 근로 시간별로 적용되는 급여 계산 시간으로 환산 계산 수식을 작성해 보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

요일별, 근로 시간대별로 적용되는 근로 시간을 급여 계산 시간으로 환산하는 계산 수식 작성 방법 썸네일 이미지.

---

목차

 

 

앞서 기록한 엑셀 시간 계산으로 근로 시간을 총 근로 시간, 야간(연장) 근로 시간, 특근(심야) 근로 시간으로 구분해서 근로 시간을 계산 하는 수식을 작성해 보았습니다.

엑셀(Excel) 시간 계산 출퇴근시간, 근로시간, 근무시간, 급여시간 계산

 

오늘 기록할 내용에서는 이렇게 근로 시간대별로 계산된 시간을 급여를 계산하기 위한 시간으로 환산하여 계산 하는 수식으로 기본 근로 시간은 1배, 야간(연장) 근로 시간에 대해서는 1.5배, 특근(심야) 근로 시간은 2배로 환산 하여 계산하고 요일별로 평일은 1배, 토요일은 기본 근로 시간 이외의 근로 시간에 대해서만 1.5배, 특근(심야) 근로 시간은 2배로 환산하여 계산한 결과값을 반환하는 수식을 기록해 보고자 합니다.

 

오늘 내용도 많은 자영업자분들, 경리, 인사, 총무부서 혹은 근로자 분들 등이 급여를 계산하고 계신분들 또는 개인적으로 필요한 분들에게 도움이 될 수 있는 기록이 되길 바랍니다.

---

1. 근로 시간을 급여 계산 시간으로 환산하는 의미.

근로시간은 급여 계산 시간으로 환산하는것은 급여를 계산 하기 위해서는 기본적으로 (기본근로시간*1)+(연장근로시간*1.5)+(특근근로시간*2) = 급여 계산 시간으로 환산하는것을 의미하며 연장 근로 시간과 특근근로시간은 평일과 주말에 의해서 기본근로시간*2 또는 기본근로시간*1.5와 같이 급여를 계산 하기 위한 시간으로 환산해야 할것입니다.

 

또한 사용자의 조직과 환경, 작성된 서식에따라서 공휴일, 대체공휴일등과 같은 요일 또는 일자에는 1.5배, 2배등과 같이 급여 계산 적용 시간이 달라질 수 있기 때문에 이번 기록 내용에서는 기본적으로 근로시간대별, 요일별에 따라 적용할 수 있는 급여 계산 시간으로 환산하는 수식을 작성하고, 추후에 실제 사용자의 질문들을 모아서 사용자의 상황과 환경에 맞게 수식을 어떻게 수정해서 사용할 수 있는지 기록해 보고자 합니다.

 

2. 근로 시간대별, 요일별 급여 계산 시간으로 환산하는 수식 작성.

근로 시간을 급여 계산 시간으로 환산 하기 위해서 급여 계산 총 근로 시간 N열과 A,B,C열에 각각 요일, 주차, 출근일수를 계산하도록 수식을 작성할 열을 추가 하였다. 수식 작성은 본문을 참조.

근로 시간을 급여 계산 적용 시간대별, 요일별로 구분하여 환산 계산 하도록 수식을 작성하기 위해서 위 이미지에서 보듯이 A, B, C열에 각각 요일, 주차, 출근 일수열과 N열에 급여 계산 총 근로시간을 환산 계산하도록 수익을 작성할 수 있도록 추가 했습니다.

 

요일과 주차, 출근 일수열을 추가한것은 어느 요일에 근로 시간이 발생했느냐에 따라서 급여 환산 근로시간이 달라지고, 주차와 출근 일수를 표기함으로써 추후 주휴수당까지 계산 할 수 있는 참조 범위를 만들고 해당일자에 속하는 주차의 총 근로 시간을 파악하기 위함입니다.

 

그럼 추가된 열이 모두 자동으로 계산되어 결과값이 환산 될 수 있는 수식을 각각 작성해 보도록 합시다.

 

2-1. 요일 계산 수식 작성.

요일을 계산 하기 위한 수식은 D열에 작성된 날짜를 참조하여 요일로 표기 할 수 있는데 앞서 기록된 글을 참조하면 좀 더 구체적인 내용을 이해 할 수 있습니다.

엑셀(Excel) 날짜를 입력하면 요일과 몇주차인지 한번에 알 수 있는 수식을 작성해 보자.

수식은 다음과 같이 A3셀에 작성 하고, 수식으로 작성되어야 할 셀까지 수식 복사 해 주시면 됩니다.

=TEXT(D3,"AAA")

요일을 표기 하는 형식을 지정하는 인수는 "AAAA"로 지정할 경우 "O요일"로 표기 되지만 N열에 요일을 참조하는 수식을 작성하기 편하게 하기 위해서 "AAA"표시 형식으로 지정해 주도록 합니다.

 

물론 사용자의 취향에 따라 "AAAA"인수를 사용하여 수식을 작성할수도 있으니 정해진 정답은 없다는것 참고해주시길 바랍니다.

 

2-2. 주차 계산 수식 작성.

주차 계산 수식 역시 D열에 작성된 날짜를 참조하여 요일로 표기 할 수 있는데 앞서 기록된 글을 참조 해 주시는것이 좋겠습니다.

엑셀(Excel) 날짜를 입력하면 요일과 몇주차인지 한번에 알 수 있는 수식을 작성해 보자.

 

수식은 다음과 같이 B3셀에 작성하고, 수식으로 작성되어야 할 셀까지 수식 복사 해 주시면 됩니다.

=IFERROR(WEEKNUM(D3)-WEEKNUM(D3-DAY(D3)+1)+1,"")

 

2-3. 출근 일수 계산 수식 작성.

출근 일수를 계산하는것은 앞서 설명한것과 같이 추후 주휴 수당을 계산할때 참조 영역으로 활용할 수 있는것으로 수식은 다음과 같이 C3셀에 작성하고, 작성된 수식은 수식으로 계산되어 결과값이 자동으로 표기 되어야 할 셀까지 수식 복사 해 주면 됩니다.

=IF(AND(COUNTIFS($E$3:$E$33,"<>",$B$3:$B$33,B3)<1),1,COUNTIFS($E$3:$E$33,"<>",$B$3:$B$33,B3))

 

추가로 회사 창립일, 공휴일등은 별도의 참조 영역을 만들어 출근일수에 포함시킬지 여부를 계산 할수있도록 해당 수식에서 추가 또는 수정하여 사용할 수 있는데, 해당 수식은 추 후 기록할 내용으로 작성할 예정이고, 오늘은 기본적인 수식과 간단한 함수로도 급여 계산과 같은 복잡한 내용을 계산 할 수 있는 수식을 작성할 수 있다는점을 중점으로 확인하고, 사용자에 맞게 수식을 변경해보는것으로 수식을 작성하는데 거부감을 줄이는데 노력해 보기로 합니다.

 

2-4. 급여 계산 근로 시간으로 환산 계산 수식 작성.

급여 계산 근로 시간으로 환산 계산하는 수식은 출근 시간과 총 근로시간, 야간(연장) 근로 시간, 특근(심야) 근로 시간, 요일, 주차를 모두 참조 영역으로 활용하여 계산 하도록 수식을 작성 할 수 있는데 수식은 N3열에 다음과 같이 작성하고, 작성된 수식은 수식으로 계산되어 결과값이 자동으로 표기 되어야 할 셀까지 수식 복사해 주시면 됩니다.

=IF($E3="연차",8, IFERROR(IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)<40,$A3="토"),SUM(($K3*1)+($L3*1.5)+($M3*2)), IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)>=40,$A3="토"),SUM(($K3*1.5)+($L3*2)+($M3*2)), IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)<40,$A3="일"),SUM(($K3*1)+($L3*1.5)+($M3*2)), IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)>=40,$A3="일"),SUM(($K3*2)+($L3*2)+($M3*2)),SUM(($K3*1)+($L3*1.5)+($M3*2)))))),""))

해당 수식역시 길게 작성 되었지만 평소 조금이라도 수식을 작성해서 엑셀 작업을 해 보았다면 충분히 누구나 사용할 수 있는 함수로 if함수, and함수, sumifs함수, sum함수, 그리고 사칙연산을 활용한 계산 수식입니다.

 

혹시 수식이 이해 되지 않는 분들을 위해서 수식이 계산되는 문단으로 나눠서 확인해 보도록 하겠습니다.

<작성된 수식의 참조 범위는 E3=출근시간, J열=총근로시간열을 참조, B열=주차열을 참조, B3셀=해당일자의 주차, A3=해당 일자의 주차, K3셀=기본근로시간, L3셀=야간(연장)근로시간, M3셀=특근(심야)근로시간>

 

① IF($E3="연차",8,

→ 출근시간이 "연차"로 입력 되어 있으면 8시간을 결과값으로 표기.

 

② IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)<40,$A3="토"),SUM(($K3*1)+($L3*1.5)+($M3*2)),

→ 해당 일자에 해당하는 주차의 총 근로 시간의 합계가 40시간 미만이고, 토요일 이라면 ((기본근로시간*1)+(연장근로시간*1.5)+(특근근로시간*2))를 계산하여 결과값으로 표기.

 

③ IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)>=40,$A3="토"),SUM(($K3*1.5)+($L3*2)+($M3*2)),

→ 해당 일자에 해당하는 주차의 총 근로 시간의 합계가 40시간 이상이고, 토요일 이라면 ((기본근로시간*1.5)+(연장근로시간*2)+(특근근로시간*2))를 계산하여 결과값으로 표기.

 

④ IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)<40,$A3="일"),SUM(($K3*1)+($L3*1.5)+($M3*2)),

→ 해당 일자에 해당하는 주차의 총 근로 시간의 합계가 40시간 미만이고, 일요일이라면 ((기본근로시간*1)+(연장근로시간*1.5)+(특근근로시간*2))를 계산하여 결과값으로 표기.

 

⑤ IF(AND(SUMIFS($J$3:$J$33,$B$3:$B$33,$B3)>=40,$A3="일"),SUM(($K3*2)+($L3*2)+($M3*2)),

→ 해당 일자에 해당하는 주차의 총 근로 시간의 합계가 40시간 이상이고, 일요일 이라면 ((기본근로시간*2)+(연장근로시간*2)+(특근근로시간*2))를 계산하여 결과값으로 표기.

 

⑥ SUM(($K3*1)+($L3*1.5)+($M3*2))))))

→ ①번부터 ⑤번까지에 해당하지 않을 경우에는 ((기본근로시간*1)+(연장근로시간*1.5)+(특근근로시간*2))를 계산하여 결과값으로 표기.

 

여기까지 급여 계산을 위한 근로 시간을 환산하여 계산하는 수식을 작성한것을 설명해 보았는데 여러분들에게 이해가 될 수 있었으면 좋겠습니다.

 

다만, 해당 수식은 해당 날짜에 속한 주차의 근로 시간이 40시간초과 미만, 또는 이상 이하 여부와 토요일, 일요일, 주중이냐에 따라 근로 시간을 급여 계산 근로시간으로 환산한것으로 급여를 계산 하는 정확한 방법에는 전문가의 도움이 필요 할 수 있습니다.

급여를 계산하는 방식과 그 결과값은 누가 계산을 하느냐에 따라 달라질 수 없고 법적 근거에 의해서 누가 계산을 하더라도 동일한 결과값으로 급여가 책정되어야 하는데, 아쉽게도 부분적으로 일상의 근로에서 법적 근거에 적법하게 급여가 지급되지는 않는것행태를 볼 수 있습니다.

 

따라서 근로자도 자신의 급여를 계산할 줄 알아야 하고, 사업주분들, 또는 급여를 계산하는 담당자분들은 급여를 계산하는 방식과 법적 근거를 명확하게 인지하여 계산해야 하고, 실제로 아르바이트 근로자와 사업주간에 빈번하게 발생하는 급여 계산 오류에 대한 입장 차이로 주휴수당 지급, 심야 근로 수당 지급등의 문제를 야기하고는 하는데, 주휴수당의 법적 지급 방법과 심야 근로 수당의 지급에 대한 원칙적인 법적 근거를 명확하게 알아야 대응을 할 수 있다는것을 참고해 주면 좋겠습니다.

 

추후에는 주휴수당의 법적 근거에 의한 지급 방법과 계산 방식, 또 엑셀로 계산 될 수 있는 수식 작성과 심야 근로의 법적 근거와 계산 방식과 같은 계산을 엑셀에서 계산 할 수 있는 수식을 작성할 수 있도록 조금 더 구체적으로 작성하는 기록을 작성해 보도록 하겠습니다.

---

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"근로시간을 급여 계산 시간으로 환산하는 수식 작성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

 

※ 근로시간을 급여 계산 시간으로 환산하는 수식 작성.

글쓰는이작가 샘플 예제 - 근로시간을 급여 계산 시간으로 환산하는 수식 작성..xlsx

0.01MB