구글 스프레드 시트 - 근무 시간 계산하기 (총 근무, 연장, 야간, 지각)

웬만하면 출퇴근 어플을 쓰는 것을 추천드립니다 ㅋㅋㅋㅋ

 

출근부를 직접 시트로 운용하고자 한다면, 아래 게시글을 참고하세요!

구글 스프레드시트 출근부 만들기

 

1. 전체적인 레이아웃 (입력해야 하는 사항)

근무자 이름, 급여 형태(월급인지, 시급인지), 급여 금액, 해당 월의 날짜, 공휴 여부, 출근 스케줄, 출근시간, 퇴근시간, 퇴근스케줄, 휴게시간입니다. 

근무지의 특성에 따라 항목이 추가될 수도 제외될 수도 있습니다.

이 계산기는 출근부를 직접 시트로 작성하거나, 엑셀로 출퇴근 시간을 출력할 수 있다는 전제하게 만들었습니다.

시간을 일일이 수기로 적기에는 너무 비효율적이니까요 ^^;

2. 총 근무시간 계산하기

출근 스케줄이 오전 9시인데 해당 근무자가 별도 보고나 필요에 의함이 아닌 조기 출근으로 8시 30분에 출근했다고 해서 그 30분을 근무시간으로 적용해 주는 업장은 없다고 생각합니다. 이 부분이 허용되면 당연히 악용하는 경우가 훨씬 많을 거고요. 출근 스케줄칸은 실제 출근시간이 스케줄보다 이른 경우, 출근시간으로 맞추기 위함입니다. 출근 시간보다 빨리 출근한 경우 수기로 출근시간을 스케줄과 동일하게 맞춥니다. 근무시간은 출근시간, 퇴근시간에 기입된 정보로 계산되기 때문입니다.

 

반응형

 

30일가량의 출근정보를 하루하루 비교하는 것도 비효율적일 수 있습니다. D4행부터 날짜 마지막 행까지 드래그하여 조건부 서식을 걸어줍니다.

입력된 출근시간이 출근 스케줄과 다르다면 초록색으로 해당 셀 배경이 바뀝니다.

퇴근시간도 마찬가지로 응용하여 넣어주면 됩니다.

 

총 근무시간을 계산하기 위해서는 총 근로 열에 '퇴근시간 - 출근시간 - 휴게시간'의 함수를 넣어줍니다.

휴게시간의 경우 '휴게종료 - 휴게시작' 으로 실제 휴게 시간을 계산하여 넣어주어도 좋고, 약속된 휴게시간이

1시간이라면 1시간을 미리 수기로 입력해 놓아도 됩니다. 대신 업무상 1시간 이내로 휴게 하는 경우, 실제 휴게시간으로 수정해주어야 합니다.

하지만 근무자들이 자주 휴게시간을 어기는 부분이 있어, 좀 더 세밀하게 측정하고자 한다면 계산식을 넣는 것이 좋습니다.

 

그리고 근무일수와 근무시간의 합계를 보기 위해 수식을 넣어보겠습니다. 그냥 SUM으로 근무시간 다 합하면 되는 거 아니야?라고 생각이 드셨다면, 오 축하드립니다. SUM을 외우셨네요. 하지만 시간의 합계는 24시간 기준으로 바뀌기 때문에, 원하는 대로 200:30 (200시간 30분)과 같은 합계는 나오지도 않습니다. 총 근무 시간을 시간 단위로 계산해야 합니다.

예를 들어 200.5시간 과 같이요.

 

자 이제 우리는 근무 시간 합계를 위해, 시트 사이드에 근무시간의 시(H)와 분(M)을 분리하는 열을 만들어 줍니다.

 

근무시 =hour(J4)

근무분 =minute(J4)

 

이렇게 입력을 하면 3월 1일 총 근무시간이 6:30 일 때, 근무시간에는 6 분에는 30이 계산됩니다.

자 그러면 이제 우리는 이 근무시간과 근무분을 분리해 놓은 행을 다 더하면 됩니다.

 

=roundup((sum(sum(X4:X34), (sum(Y4:Y34))/60)),1)

 

X열이 시간이 계산된 열이고, Y열이 분이 계산된 열입니다.

시간은 그대로 더하면 됩니다만 분의 경우 합계를 다시 60으로 나누어 시간단위로 바꿔줍니다.

그리고 그 둘을 다시 SUM으로 더해주고, 그렇게 계산된 근무 시간을 소수점 2번째 자리에서 반올림해줍니다.

이것은 선택하기 나름입니다만, 계산된 시간보다 적게 줬을 때의 반발을 겪으니 반올림해서 주는 것이 큰 차이도 없고 심적으로 스트레스도 덜 받습니다.

 

근무 일수는 Counta 함수를 사용합니다.

 

=counta(D4:D34)

 

구글 스프레드시트 함수 (엑셀 함수) countif, counta, countifs

 

3. 연장시간 계산하기 (계약 근무 시간 기준)

만약 계약 근무시간이 1일 8시간이라고 했을 때, 8시 이상의 근무는 연장근무로 들어가게 됩니다.

그래서 퇴근시간을 스케줄과 맞추는 과정이 필요합니다. 별도의 보고 없이 불필요한 연장을 반복하게 되는 경우가 생길 수 있습니다. 

 

연장시간계산은 아까 우리가 분리해 놓은 근무시간/분 열을 활용합니다.

연장시간의 '시'는 분리된 근무시간의 '시'가 8보다 클 경우 계산합니다. 근무시간에서 8을 뺍니다.

연장시간의 '분'은 분리된 근무시간의 '시'가 8보다 같거나, 클 경우에만 그대로 불러옵니다.

 

그리고 상단에 각각의 계산된 연장 시간과 분을 합해줍니다.

4. 야간 근무 시간 계산하기 (22시 이후)

연장시간과 계산법이 비슷합니다만 기준 시간이 8시간 근무가 기준이 아니라 22시(밤10시) 이후 근무 시간입니다.

근무시간을 분리한 것처럼, 퇴근시간을 시와 분으로 분리해줍니다.

그리고 그 퇴근시간의 '시' 부분이 22보다 클 경우에만 해당 시간 -22를 해줍니다.

그리고 퇴근시간의 '분'은 퇴근시간의 '시'부분이 22와 같거나 큰 경우 그대로 불러옵니다.

 

퇴근시 =hour(E4)

퇴근분 =minute(E4)

 

야간시 =if(D4="","",if(V4<22,"",if(V4=22,"",V4-22)))

야간분 =if(D4="","",if(V4<22,"",W4))

 

그리고 마찬가지로 상단에 각각의 계산된 야간시간과 분을 합해줍니다.

 

위의 IF수식이 이해가 안된다면, 아래 게시글을 참고하세요.

구글 스프레드시트 함수 (엑셀) IF, IFS

5. 공휴시간 계산하기

공휴일은 날짜옆에 드롭다운 메뉴로 '공휴'를 선택하게 만들어도 되고, 귀찮거나 방법을 모른다면 그냥 수기로 '공휴'라고 기입하셔도 됩니다.

 

공휴시간의 시는 분리해놓은 근무시간의 시를 그대로 사용하면 됩니다. 분도 마찬가지 입니다.

 

=if(B4="","",if(B4="공휴",X4))

 

공휴일 체크하는 부분이 '공휴'라면 근무한 시간을 그대로 불러오고, 분도 마찬가지로 기입하시면 됩니다.

 

6. 지각시간 계산하기

지각를 한번정도 한 다면 상관없지만 여러번 반복적으로 하는 경우에는 좀 주의깊게 체크해서 경고를 할 필요가 있습니다.

이 때 출근스케줄이 필요합니다. 

출근 스케줄 이전에 온 출근시간은 출근 스케줄과 맞추고, 그 이후에 온 시간을 그대로 두면 지각시간에 체크가 됩니다.

출근시간-출근 스케줄 함수를 넣습니다.

 

=if(D4="","",if(C4-D4>0,"",if(C4-D4=0,"",D4-C4)))

 

상단 어딘가 빈칸에 총 지각시간을 다 합치는 수식을 넣고, 총 근무 시와 분을 시간단위로 수정한 것처럼 분리해서 합해줍니다.

 

=sum(hour(H2),(minute(H2)/60))