구글 스프레드시트 연차 계산기 만들기 (자동 잔여연차 계산)

가장 추천하는 것은 프로그램을 구입하는 것입니다.
출퇴근과 휴가등이 자동으로 기록이 되는 프로그램이 시중에 많이 나와있습니다.
 
오늘은 프로그램이나 유료 사이트를 사용하지 않고, 임시적으로 무료로 사용할 수 있는 연차계산기를 만들어보겠습니다.
 
준비물 : 구글 스프레드 시트, 복잡한 수식을 이해하려는 노력과 인내심
 

1. 연차계산기 틀 만들기

 
계산을 위한 표를 만들 때는, 보여야 하는 값을 기준으로 그 값을 산출하기 위한 값까지 기록할 수 있게 만들어야합니다.

위와 같이 예제로 만들어 봤습니다.
제가 좋아하는 아이돌,배우 이름을 다 적어넣었더니 남초회사가 됐네요. 껄껄 (이런 회사 만들고 싶다 광광)
잔여 연차를 계산하기 위한 기본 정보 필터를 적어 넣습니다.
 
1) 이름 : 근무자 이름
2) 입사일 : 입사한 날짜
3) 급여전환일 : (월급 계약 시 급여에 연차보상액이 포함되는 경우 해당)시급계약에서 월급 계약으로 전환된 날짜. 처음부터 월급인 경우 입사일과 동일하게 작성
4) 퇴사일 : 퇴사한 날짜를 입력
5) 총 근무개월 : 총 근무한 기간이 개월수로 계산
5) 급여 근무개월 : 총 근무개월 중 급여 계약으로 근무한 개월 수
6) 잔여연차 : 오늘 날짜 기준 사용 가능한, 혹은 보상 해야할 잔여 연차
7) 발생연차 : 입사일 기준으로 오늘까지 발생한 총 연차
8) 급여포함 : 급여에 연차가 포함된 경우 급여 계약 기간만큼 차감하기 위해 계산
9) 소모연차 : 우측 빈칸에 연차를 사용할 때 마다 날짜를 기록하는 경우 소모 연차에 일수로 계산됨
 
월급에 연차보상액이 포함되지 않는 경우, 해당 칸은 없애고 만들면 됩니다.
 

2. 연차 발생 기준 알기

 
근로기준법상 1년 미만 근무자는 만근 시 다음 달에 1개의 연차가 발생합니다. 그래서 근무 첫 해의 1년간은 총 11개의 연차가 발생합니다. 그리고 연속 근무가 1년을 넘어가는 순간 15개의 연차가 주어집니다. 첫 해의 11개의 연차를 사용하지 않았다면 총 26개의 연차를 가지게 됩니다.
 
(첫 해의 11개 연차는 만근 기준으로, 결근이 발생한 월에 대한 연차는 발생하지 않습니다.)
 
그리고 이 15개의 연차는 2년 마다 1개씩 늘어납니다.
즉 11 - 15 - 15 - 16 - 16 - 17 - .. 25 이런 방식으로 최대 25개까지 발생할 수 있습니다.
 
연차보상비를 지급하는 시기는 회사와 계약사항마다 다를 것이기 때문에 언급하지 않겠습니다.
 

3. 실제 근무일수에 따른 연차 발생 계산하기 (자동)

 
자 이제 계산기 표도 만들었고, 연차 발생 기준도 알았습니다.
그렇다면 본격적으로 함수를 사용하여 계산기를 작동시켜 보겠습니다.
 

 
1) 총 근무개월    =if(A2="","",if(D2<>"",datedif(B2,D2,"M"),DATEDIF(B2,TODAY(),"M")))
 
입사일부터 오늘까지의 총 근무기간을 개월수로 나타나게끔 함수를 적용했습니다.
if 함수에 대한 설명은 게시물을 링크하겠습니다.
 
2023.01.25 - [구글 스프레드 시트] - 구글 스프레드시트 함수 (엑셀) IF, IFS

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

 
=if(A2="",""
어떤 함수를 사용하던, 장기적으로 사용하는 시트의 경우 에러가 떠있는 화면은 지저분해 보입니다.
괜히 신경쓰이기도 하구요. 그런데 값이 제대로 입력이 되어있지 않거나, 계산해야 하는 대상 셀이 빈칸인 경우 에러 표시가 뜹니다. 그렇기 때문에 계산해야 할 셀이 빈칸인 경우, 결과 셀도 빈칸으로 두라는 함수를 항상 씁니다.
 
,if(D2<>"",
<>는 = 의 반대 의미 입니다. = 의 경우 뭐뭐와 같다면, 이라는 의미 이지만 <>는 뭐뭐와 같지 않다면, 이라는 뜻입니다.
즉 " ", d2 셀이 빈칸이 아니라면~ 이라는 의미입니다. 은근 자주 쓰이는 기호입니다.
 
datedif(B2,D2,"M")
 
2023.03.15 - [구글 스프레드 시트] - 구글 스프레드시트 엑셀 함수 - 날짜계산 date, datedif, days

구글 스프레드시트 엑셀 함수 - 날짜계산 date, datedif, days

기간 계산 함수 입니다.  시작일, 종료일, 단위를 기록하면 원하는 단위로 결과값을 도출해줍니다.
 
앞의 함수와 연결해서 보자면, '퇴사일' 셀이 빈칸이 아니라 값이 있다면, '퇴사일 - 입사일'을 계산해서 'M' 개월수로 나타내달라고 요청하는 겁니다.
 
DATEDIF(B2,TODAY(),"M")   *함수의 대문자 소문자 사용은 쿼리 속 함수가 아닌 이상 신경쓰지 않으셔도 됩니다.
 
역시 앞의 함수와 연결해서 보겠습니다. 퇴사일 셀이 빈칸이 아니라면 퇴사-입사를 계산했습니다. 그런데 퇴사일 칸이 빈칸인 경우, 즉 지금 계속해서 근무를 하고 있는 경우의 근무기간 계산입니다.
'오늘 날짜 - 입사일'을 계산해서 개월 수도 나타냈습니다.
 
today() 가 오늘 날짜를 나타내는 함수 입니다. 빈 셀에 =today() 를 입력하면 자동으로 매일 오늘 날짜로 갱신이 됩니다.
그래서 이 연차계산기가 매일 오늘 날짜 기준으로 잔여연차를 자동으로 계산해줄 수 있습니다.
 
급여 전환일도 동일하게 대상 셀만 바꿔서 적용하면 됩니다.
급여 포함 역시 급여 전환기간과 동일 하겠죠?
 
2) 발생연차

 
발생연차는 우리가 알고 싶어하는 잔여연차의 기본이 되는 값입니다.
그래서 지금 이 근무자에게 총 몇개의 연차가 발생 했는가에 대한 계산 값인데, 이 부분 함수를 위해서 위의 연차발생 기준을 알아야합니다. 
 
=if(A2="","",IF(E2<13,E2-1,if(E2>12,26,if(E2>24,41,))))
 
IF(E2<13,E2-1
총 근무기간이 13개월 미만이면, 발생연차는 총 근무개월 -1 로 계산한다. 12개월까지는 1달 만근 시 다음 달 연차 1개 발생이기 때문에 근무개월에서 1개를 뺍니다.
 
if(E2>12,26
총 근무기간이 12개월 초과면, 1년간 총 11개의 연차, 그리고 2년차에 접어들면서 15개가 발생하므로 총 26개가 됩니다.
 
if(E2>24,41
총 근무기간이 24개월 초과면, 3년차에 또 15개의 연차가 발생하므로 총 41개의 연차를 보유하게 됩니다. 3년 이상 근무하는 직원들이 많아진다면, if를 활용하여 조건절을 늘려나가면 되겠습니다.
 

 

 
3) 소모연차

=counta(K2:AS2)
 
k열 부터 뒤로는 빈칸이 나열되있습니다. 연차를 사용한 직원이 있다면, 해당 직원의 빈칸에 순서대로 연차 사용 날짜를 기록하면 됩니다. 첫 연차는 k열에 두번째는 L 열에 기록하면 됩니다. counta 는 지정한 범위에 데이터 값이 있기만 하면 그 갯수를 세어줍니다. 그래서 기록만 잘 해 놓는다면, 소모연차를 알아서 계산해줍니다.
 
2023.02.06 - [구글 스프레드 시트] - 구글 스프레드시트 함수 (엑셀 함수) countif, counta, countifs

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

 
4) 잔여연차
 
굳이 함수를 쓰지 않아도, 발생연차-급여포함-소모연차 가 잔여연차가 됩니다.
 
 
실제 업무에서 사용하는 시트들을 많이 남겨보려고 하고 있습니다.
혹시나 만들고 싶은 시트가 있다면 언제든 의견 남겨주십셔!!!