[엑셀] 급여관리 - 근로시간 / 근무시간 자동 계산하는 수식 작성 방법!!

안녕하세요.

행아아빠 입니다.

 

이번 엑셀 내용은

엑셀 함수를 이용해서 급여관리 하는 방법으로 근무시간 또는 근로시간을 자동으로 계산하는 수식 작성 방법 입니다.

 

보통 회사의 전산이나 별도의 급여관리 프로그램을 많이 사용할 것 같지만

은근히 엑셀로 급여관리 하시는 분들이 많이 계신것 같습니다.

 

근무시간을 계산하고 그 결과값들을 자동으로 계산하여 값으로 표기 하기 위해 사용할 함수는

If함수, OR함수, AND함수, Time함수, Min함수, Max함수와 그 외 몇몇 IFError함수와 같은 간단한 함수들을 사용할 겁니다.

 

 

그럼 오늘 내용도 많은 분들께 전달되어 도움이 되시기를 바라겠습니다.

---

 

■ IF, OR, AND, TIME, MIN, MAX함수의 기본 수식 작성 방법.

 

① IF함수

    IF함수는 활용도가 굉장히 많은 함수이기 때문에 꼭 알아두셔야 할 함수 중 하나 입니다.

    IF함수의 사용 방법 및 예시는 기존에 설명드린적이 있기에 아래 링크에서 간단하게 확인해 주시면 되겠습니다.

 

[엑셀] 날짜를 자동으로 입력하는 수식 작성 방법!!

https://hang-a-fafa.tistory.com/9

 

 

② OR함수

    OR함수는 참값과 거짓값 중에 참값이 하나라도 있다면 True를,

    모든값이 거짓값 이라면 False값을 반환하는 함수 입니다.

 

    예를들어,

    =OR(1+1=3,2+2=4) 라는 수식을 작성 했을때

    결과값은 True값을 결과값으로 얻을 수 있습니다.

    1+1=2이고, 2+2=4인데, 예시의 인수중 2+2=4라는 참값이 하나라도 존재 하기 때문입니다.

 

    만약,

    =OR(1+1=3,2+2=5) 라는 수식으로 작성을 한다면,

    그 결과값은 False값이 결과값으로 표기 될 것 입니다.

    1+1=2이고, 2+2=4이기 때문에 작성된 인수 모두가 거짓값이기 때문입니다.

 

    보통은 OR함수를 단독으로 사용하는 경우는 많지 않고, IF함수와 종종 사용되곤 합니다.

 

③ AND함수

    AND함수는 OR함수와 비슷하지만 반대라고 생각하시면 될 것 같습니다.

    AND함수에 사용된 인수 모두가 참값이어야지만 True값을 결과값으로 표기하고,

    인수 중에 하나라도 거짓값이라면 False값을 결과값으로 반환하게 됩니다.

 

    예를들어,

    =AND(1+1=3,2+2=4) 라는 수식을 작성했을때

    그 결과값은 False값을 결과값으로 표기 하게 됩니다.

    OR함수는 True값을 결과값으로 표기하는 반면에, AND함수는 False값을 결과값으로 표기하게 되는데요,

    인수 중 1+1=3 이라는 거짓값이 하나라도 존재 하기 때문입니다.

 

    =AND(1+1=2,2+2=4) 라는 수식으로 작성을 해야

    AND함수에서는 결과값을 True값으로 표기 할 수 있는 것 입니다.

 

 

    AND함수 역시 OR함수 처럼 단독으로 사용하는 일은 많지 않고, IF함수와 같이 논리값을 찾기 위해서 종종 사용되곤 합니다.

 

④ Time함수

    Time함수는 특정 시간을 나타내는 소수를 반환하는 함수 입니다.

    함수가 입력되기 전의 셀이 일반 서식을 갖고 있더라도 결과값은 날짜 서식으로 지정됩니다.

   

    함수 수식 작성 방법은

    =Time(시간단위, 분단위, 초단위) ← 이와 같은 형태로 작성해 주시면 됩니다.

 

    만약, A1셀에 12:01:52라는 값이 입력되어 있다면,

    =Time(A1,A1,A1) 으로 수식을 작성해 주시면 그 결과값은 A1에 작성한 값 그대로 12:01:52라는 값을 표기할 수 있습니다.

 

⑤  Min함수

    Min함수는 작성된 값들 중 최소값을 반환하는 함수 입니다.

 

    만약,

    A1셀 ~ A6셀까지 1~6까지 입력되어 있다고 가정할 경우

    =Min(A1:A6) 이라는 수식을 작성해 주시면,

    그 결과값은 1~6의 값 중에서 최소값인 1의 값을 결과값으로 반환하여 표기하게 됩니다.

 

⑥ Max함수

    Max함수는 Min함수와 반대로 작성된 값들 중에서 최대값을 반환하는 함수 입니다.

 

    만약,

    A1셀 ~ A6셀까지 1~6까지의 값들로 입력되어 있다고 가정할 경우

    =Max(A1:A6) 의 수식을 작성해 주시면,

    그 결과값은 1~6의 값 중에서 최대값인 6을 결과값으로 반환하여 표기하게 됩니다.

---

 

■ 출,퇴근 시간을 기준으로

    총 근무시간, 기본근로시간, 야근근로시간, 특근(심야)근로시간

    자동으로 계산하는 수식 작성 방법.

 

 

▲ 빨간 네모박스 안에 들어갈 시간들을 정수의 형태로 수식을 작성해서 자동으로 계산하여 입력되도록 하고자 하는 것 입니다.

 

    우선, 출/퇴근 시간의 경우에는 5가지의 경우의 수로 수식을 작성하기 위한 조건을 생각해 볼 수 있겠습니다.

 

    ① 점심시간을 뺀 기본근로시간이 8시간인 경우.

    ② 점심과 저녁시간을 뺀 기본근로시간이 8시간과 야간근로시간이 1시간인 경우.

    ③ 점심과 저녁시간을 뺀 기본근로시간이 8시간과 야간근로시간이 4시간인경우, 그리고 특근근로시간이 1시간인 경우.

    ④ 저녁시간을 뺀 특근근로시간이 8시간인 경우.

    ⑤ 저녁시간을 뺀 특근근로시간이 8시간이고, 기본근로시간이 1시간인 경우.

 

    위 5가지의 경우를 고려해서 예시를 작성해 보도록 하겠습니다.

    보통은 1~3번에 해당하겠지만, 제조업의 경우 근래에는 주 52시간제가 도입되면서 많이 없어지긴 했지만,

    2교대 혹은 심야근무의 경우 4번과 5번에 해당하는 경우로 생각해 볼 수 있겠습니다.

 

    그리고 연봉제의 경우에는 근무시간이 중요하지는 않겠지만,

    내가 근무한 총 근로시간대비 시급으로 계산해보면 얼마일까?? 라고 궁금하신분들 한번 계산해 볼 수 있겠습니다.

 

    자 그럼 각각의 경우에 오류 없이 모두 자동으로 시간이 계산되어

   각각의 셀에 입력될 수 있도록 수식을 작성해 보도록 하겠습니다.

---

 

▶ 총근무시간 수식 작성 방법.

 

총근무시간은 (퇴근시간 - 출근시간) - 휴식시간(점심 or 저녁시간)이 되겠는데요,

그럼 출근시간은 B3셀이고, 퇴근시간은 C3셀에 작성되어 있기 때문에

=C3-B3 이라고 수식을 작성해 주시면 결과값은 9:00 이라고 표기됩니다.

 

우리가 급여를 계산할 때는 시간을 정수로 계산하니 엑셀에서도 결과값이 정수로 나와야 보기도 편하고, 계산도 편리할 수 있겠죠?!

 

그래서 하루 24시간을 분으로 바꾸면 1,440분이 되기 때문에

=((C3-B3)*1440)/60 이라는 수식으로 작성해 주시면,

결과값으로 9라는 값을 표기할 수 있습니다.

 

 

▲ 하지만... 수식을 입력했다고 해서 바로 결과값이 9로 나오는 것은 아닙니다.

    위 그림처럼 총근무시간의 결과값이 0:00이라고 입력된 것을 볼 수 있습니다.

    이것을 정수 형태로 바꾸어 주기 위해서는

    ① 네모박스를 사용자지정에서 표시형식을 일반으로 변경해 주시면 9라는 결과값을 표기 할 수 있습니다.

 

▲ 하지만 여기에서도 문제는 있습니다.

    보통은 퇴근시간이 출근시간보다 큰 값이기 때문에,

    퇴근시간에서 출근시간을 빼주시면 되겠지만,

    4번과 5번의 경우에서 처럼 퇴근시간이 출근시간보다 작은경우에는 수식을 입력했을때

    값을 입력할 수 없는 ####...으로 표기 됩니다.

 

    단순 더하기 빼기로는 모든 경우에 값이 출력될 수 없는 수식이 되기 때문에

    수식을 모든 경우에 값이 표기될 수 있도록 수정해보면 아래와 같이 작성할 수 있습니다.

    =IF(B3<C3,(C3-B3)*1440,IF(OR(B3="",C3=""),"",IF(B3>C3,(1+C3-B3)*1440)))/60)+Sum(-D3-E3)

 

    작성한 수식에서도 경우의 수를 생각해서 작성한 것으로

    1. 만약, 출근시간과 퇴근시간이 공란일경우.

    2. 만약, 출근시간이 퇴근시간보다 작을 경우.

    3. 만약, 출근시간이 퇴근시간보다 클 경우.

위 3가지의 경우에 따라 각각 계산하는 방법을 달리 하라는 수식 구문이 되겠습니다.

 

그리고 마지막으로 수식이 완성되었다면,

표시형식은 사용자지정에서 일반으로 변경해 주시면 정수의 값으로 각각의 근로시간을 자동 계산해서 입력 시킬 수 있습니다.

---

 

■ 기본근무시간 수식 작성 방법.

 

기본근무시간은 비근로시간을 제외한 최대 8시간까지 입니다.

8시간이 넘어간 근무시간은 야근 또는 특근 시간으로 넘어가게 되기 때문입니다.

 

기본근무시간은

총근무시간이 8시간을 넘는다면, 8시간이고,

총근무시간이 8시간을 넘지 않는다면, 총근무시간이 기본근무시간이 되겠습니다.

 

어렵게 생각할 것 없이, 총근무시간에서 야근시간과 특근시간을 빼준 나머지 시간이 기본근무시간이라고 생각하면 간단할 것 같습니다.

 

 

▲ 기본근무시간 셀에 들어갈 수식을 작성해보면,

    그림에서 보는것과 같ㅌ이 총근무시간 - 비근로시간 - 연장근무시간 - 특근근무시간으로 작성해 주시면 됩니다.

    =((IF(B3<C3,(C3-B3)*1440,IF(OR(B3="",C3=""),"",IF(B3>C3,(1+C3-B3)*1440)))/60)-D3-E3)-H3-I3

 

    나머지 셀은 수식복사 또는 드래그해서 수식으로 채워주시면 자동으로 계산되어 원하는 값이 입력되어 집니다.

    만약, 표시형식이 00:00과 같은 시간형식으로 표기 된다면

    위에서 설명드렸듯이 표기형식을 사용자지정에서 일반으로 바꾸어 주시면 됩니다.

---

 

■ 연장근무시간 수식 작성 방법.

 

연장근무시간은 총근무시간에서 기본근무시간과 특근시간을 빼주면 연장근무시간이 나오겠지만

수식으로 작성할 때에는 몇가지 조건을 생각해 볼 수 있겠습니다.

 

1. 기본근무시간이 8시간 이하일 경우.

2. 기본근무시간이 8시간 이상일 경우.

3. 출근 또는 퇴근시간이 공란일 경우.

 

우선은 3가지의 경우를 생각해 볼 수 있겠습니다.

3가지의 경우를 생각해서 수식을 작성하고 추가적으로 경우의 수가 더 생긴다면, 추가해서 수식을 작성해 주시면 되겠습니다.

 

 

▲ 위 그림처럼 수식을 작성 하시면 되겠습니다.

    =IF(IF(OR(B3="",C3=""),"",

    IF(AND(B3<C3,((C3-B3)*1440)/60-D3<=8),0,

    IF(AND(B3<C3,((C3-B3)*1440)/60>8),((C3-B3)*1440)/60-8-E3-D3,

    IF(B3>C3,((1+C3-B3)*1440)/60-8-D3-E3)))-I3)<0,0,

    IF(OR(B3="",C3=""),"",

    IF(AND(B3<C3,((C3-B3)*1440)/60-D3<=8),0,

    IF(AND(B3<C3,((C3-B3)*1440)/60>8),((C3-B3)*1440)/60-8-E3-D3,

    IF(B3>C3,((1+C3-B3)*1440)/60-8-D3-E3)))-I3))

 

    위 수식이 복잡해 보일 수 있기 때문에, 수식을 단락별로 끊어서 살펴 보겠습니다.

    총근무시간을 작성한 수식에서 추가된 조건을 응용해서 작성했으니 총근무시간 수식을 이해 하셨다면

    큰 어려움 없으시리라 생각됩니다.

 

    수식을 단락별로 확인해 보면 아래와 같습니다.

    1. IF(IF(OR(B3="",C3=""),"",

        → 출근시간과 퇴근시간중 하나라도 공란이라면, 해당셀을 공란으로 표기하고,

            공란이 아니라면, 다음 수식을 계산. 

 

    2. IF(AND(B3<C3,((C3-B3)*1440)/60-D3<=8),0,

       → 출근시간이 퇴근시간보다 작고, 퇴근시간에서 출근시간을 뺀 시간이 8시간보다 작거나 같다면,

           0으로 표기하고, 그렇지 않으면 다음 수식을 계산.

 

    3. IF(AND(B3<C3,((C3-B3)*1440)/60>8),((C3-B3)*1440)/60-8-E3-D3,

       → 출근시간이 퇴근시간보다 작고, 퇴근시간에서 출근시간을 뺀 시간이 8시간을 초과한다면,

           퇴근시간 - 출근시간 - 8시간 - 저녁시간 - 점심시간의 값을 계산하고, 그렇지 않으면 다음 수식을 계산.

 

    4. IF(B3>C3,((1+C3-B3)*1440)/60-8-D3-E3)))-I3)<0,0,

       → 출근시간이 퇴근시간보다 크다면, 비근로시간을 뺀 나머지 근로시간을 계산하고,

           그렇지 않고 1번 ~ 4번까지의 계산값이 0보다 작다면, 0의 값으로 계산.

           1번 ~ 4번까지의 값이 아니라면 다음 5번부터의 수식을 계산.

 

    5. IF(OR(B3="",C3=""),"",

       → 출근시간, 퇴근시간 두 셀중 하나라도 공란이라면, 공란으로 표기하고,

           그렇지 않으면 다음 수식을 계산.

 

    6. IF(AND(B3<C3,((C3-B3)*1440)/60-D3<=8),0,

       → 출근시간이 퇴근시간보다 작고, 비근로시간을 제외한 총근로시간이 8시간보다 작거나 같다면 0으로 계산하고,

           그렇지 않으면 다음 수식을 계산. 

 

    7. IF(AND(B3<C3,((C3-B3)*1440)/60>8),((C3-B3)*1440)/60-8-E3-D3,

       → 출근시간이 퇴근시간보다 작고, 비근로시간을 제외한 총근로시간이 8시간보다 크다면,

           총근로시간에서 8시간을 빼고 비근로시간인 점심, 저녁시간을 뺀 값을 입력하고,

           그렇지 않으면 다음 수식을 계산. 

 

    8. IF(B3>C3,((1+C3-B3)*1440)/60-8-D3-E3)))-I3))

       → 출근시간이 퇴근시간보다 크다면,

           총근로시간에서 8시간을 빼고 비근로시간인 점심, 저녁시간을 빼고, 추가적으로 특근시간을 뺀 값을 입력. 

 

수식이 길게 작성되어 있기 때문에 수식의 단락을 구분하는 괄호를 잘 구분해서 단락별로 수식을 끊어서 본다면,

복잡하게 보이던 수식이 간단한 수식의 나열로 볼 수 있기 때문에 작성하는데도 어려움 없이 작성 하실 수 있을 겁니다.

---

 

■ 특근(심야 - 22:00 ~ 다음날 06:00)근무시간 수식 작성 방법.

 

 통상적으로 특근시간은 22:00부터 다음날 06:00까지 적용되기 때문에 해당시간을 기준으로 설명 드리겠습니다.

회사마다 각각의 회사규정에 따라 조금씩 다르게 적용될 수 있으니 참고하셔서 여러분 회사에 맞게 수정해서 사용하시면 되겠습니다.

 

 특근시간은 급여계산상 2배를 해 주는 근로시간을 특근시간이라고 저는 지칭 하겠습니다.

제가 작성한 표에는 특근이 발생하는 시간대에서는 비근로시간이 없다고 가정하여 작성한 것이니

혹여나 특근이 발생한 시간대에 휴식시간 또는 간식시간과 같은 비근로시간이 발생한다면,

지금부터 작성할 수식에서 추가적으로 특근시간에 발생한 비근로시간을 빼주면 될 것 입니다.

 

 

 

▲ 특근근무시간을 계산하는 수식은

    =(IF(OR(B3="",C3=""),"",

    IF(C3<B3,Min(Time(6,0,0),C3)+1,Max(Time(22,0,0),C3))-Max(B3,Time(22,0,0))))*(1440/60)

    위 수식과 같이 작성을 해 주시면 되겠습니다.

 

만약, 말씀 드린것과 같이 특근시간대에 휴식시간등과 같은 비근로시간이 발생한다면 위 수식의 결과값에서 추가적으로

특근시간에 발생한 비근로시간을 빼 주시면 되겠습니다.

---

 

이상으로 오늘의 엑셀 함수를 이용한 급여관리중 근무시간 또는 근로시간을 자동으로 계산하는 수식 작성 방법이였습니다.

부디 많은 분들께 도움이 되어 전달 되었으면 좋겠습니다.

 

그럼 오늘 하루도 기분 좋은 하루 되시길 바라겠습니다.^^!