회계년도 연차계산 포스트를 보고 나면

각 회사 사정에 맞는 회계년도 연차계산 엑셀 양식을 만들 수 있습니다.

날짜함수

DATE/YEAR/MONTH/

TODAY/DATEDIF

가 사용되었습니다!

사용법등을 알 수 있습니다.

논리함수

IFS/AND

가 사용되었습니다!

사용법등을 알 수 있습니다.

검색함수

HLOOKUP

가 사용되었습니다!

사용법등을 알 수 있습니다.

연차예제.xlsx를 클릭하여 예제를 다운받을 수 있습니다.

엑셀 2016이하 버전에서 IFS함수는 지원이 되지 않아 엑셀 2016이하 사용자는

예제 파일이 제대로 열리지 않을 수 있습니다.

IFS함수를 IF로 고쳐 사용하시거나 구글 스프레드시트 링크를 이용하여 주세요.

사용방법은 시트를 복사하여 사용 가능합니다.

회계년도 연차계산 예제 링크 이동


안녕하세요, 

이번 포스트에서는 고용노동부에서 제공하고 있는

‘1년 미만 근로자 등에 대한 연차휴가 보장 확대 관련 개정 근로기준법 설명자료’

참고하여 연차일수를 계산하는 방법을 알아보도록 하겠습니다.

(포스트에 첨부함)


개정법 적용대상이 아닌 2017.05.30 이전 대상자의 연차계산은 따로 계산하셔야 됩니다. 

(예제에서는 개정법을 기준으로 진행됨.)


첨부된 근로기준법 내용을 한번 읽고 예제에 사용된 수식을 보는 것을 추천드립니다.

본 포스트에서 사용하고 있는 연차일수 관리 방법은 입사년도, 회계년도 중 회계년도를 통한 연차일수를 구하는 방법입니다.

 

사용된 주요 함수 

1.DATEDIF함수

2.HLOOKUP함수

기타함수

YEAR함수,MONTH함수,DATE함수,TODAY함수,IFS함수,AND함수등이 사용되었습니다.


각 회사마다 연차가 다 다르므로 회사 사정에 맞게 수정하여 쓰시면 됩니다.

 

IFS함수는 엑셀 2019버전 이상에서만 지원합니다!

엑셀 2016이하 버전 사용자분들은 예제다운시 수식값이 제대로 출력되지 않습니다.

이 때에는 IFS함수대신 IF함수를 사용하시면 정상적으로 사용가능합니다!

 

IFS함수는 조건1,참일때값1,조건2,참일때값2… 로 되어있는 함수입니다.

IF(조건1,참값,IF(조건2,참값,IF(조건3,참값… 등으로 대체가 가능합니다.

 

IFS함수등이 사용이 안돼 수식값이 오류로 반환된다면, IF함수로 대체해보세요!!

 

해당 예제를 사용하여 발생하는 문제에 대해서는 책임지지 않습니다.

 

2020.01.27 추가 — 연차관리대장만들기 포스트에서 양식까지 제공하고 있습니다.

양식이 필요하신분은 연차관리대장만들기 포스트를 눌러주세요!!


본 포스트에서 회계연도로 연차일수를 구하는 방법을 알아보기 전에,

간단하게 근로기준법에 나와있는 내용과 고용노동부에서 제공하고 있는

‘1년 미만 근로자 등에 대한 연차휴가 보장 확대 관련 개정 근로기준법 설명자료’ 2018.5 중 회계연도로 연차일수를 구하는 방법의 내용을 참고하시기 바랍니다.

회계연도 구하는 방법 역시 고용노동부에서 제공한 방법으로 계산된 점 참고하시기 바랍니다. 

3년이상 계속 근무한 경우 연차 가산

연차일수RR
만들어볼 예제
연차수식1
=IFS(F8="","")의 수식 결과값

예제에 사용된 수식은 다음과 같습니다.

=

IFS(F8=“”,“”)

=IFS(F8=””,”” -> F8(입사일)셀에 값이 없다면 “”공백을 표시합니다.

 

 양식을 깔끔하게 보이게 하기 위해 사용하였습니다.

연차일수수식2
YEAR(TODAY())=YEAR(F8), MONTH(TODAY())–MONTH(F8)의 결과값

수식YEAR(TODAY())=YEAR(F8),

MONTH(TODAY())MONTH(F8)

YEAR(TODAY())=YEAR(F8) -> 오늘 날짜의 연도가 입사일(F8) 중 입사년도와 같다면,

MONTH(TODAY())-MONTH(F8) -> 오늘 날짜 중 월, 즉 당월에서 입사월을 차감합니다.

 

HLOOKUP 범위중 C3:N4(파란범위)는 입사월에 따른 연차갯수입니다.

종합해서 말해보면 당해년도에서 입사년도를 차감한 값이 1년과 같고, 입사월이 1월이면 15의 연차갯수를 반환하는 수식입니다.

연차수식2
AND(YEAR(TODAY())-YEAR(F8)=1,11-HLOOKUP(MONTH(F8),$C$3:$N$4,2,0)=0),15의 결과값

수식

AND(YEAR(TODAY())YEAR(F8)=1

-> 당해년도에서 입사년도를 차감한게 1과 같고,

11-HLOOKUP(MONTH(F8),$C$3:$N$4,2,0)=0),15,

-> (11은 중도입사자가 1년에 받을 수 있는 총 연차갯수입니다.)

총 연차갯수에서 F8에 입사월을 C3:N4범위 중 2번째 행에서 찾습니다.

찾은 값을 11-(찾은값) 차감하여 주고 그 차감한 값이 0이면 15를 반환합니다.

연차일수수식3

AND(YEAR(TODAY())YEAR(F8)=1,11HLOOKUP(MONTH(F8),$C$3:$N$4,2,0)<>0),

 

당해년도에서 입사년도를 차감한 값이 1년과 같고, 입사월이 1월이 아니면

 

(11HLOOKUP(MONTH(F8),$C$3:$N$4,2,0))+(DATEDIF(F8,DATE(YEAR(F8),12,31),“D”)+1)/365*15, 

 

총 연차갯수에서 입사년도에 사용할 수 있는 연차갯수를 차감하고 남은 값을 다음의 수식과 더합니다.

 

DATEDIF함수로 입사년도(작년)에 근속한 근속일수를 구한 후 고용노동부에서 제공한 산식을 사용합니다. (15*근속일수/365)

연차일수수식4
YEAR(TODAY())-YEAR(F8)=2,15의 결과값

수식

YEAR(TODAY())-YEAR(F8)=2,15, 

 

 당해년도에서 입사연도를 차감한 연도가 2년과 같다면, 15를 부여합니다.

123szxc

우선 회계년도에선 1월1일 입사자가 아닌 이상 모두 중도 입사자에 해당됩니다.

회계년도를 사용한다면 중도 입사자는 익년 1월1일에 1년차부터 시작하게됩니다.

따라서 1월1일 입사자는 1월1일이 되면 1년을 초과하게 되므로 중도입사자와는 다르게

연차가산이 1년이 더 빠릅니다.

다음의 예시를 보겠습니다. 

ex) 2017.01.01 입사자의 연차일이 16일 되는 연도는?

2018.01 15개

2019.01 15개

2020.01 16개

1월1일 입사자는 전년도 12월31일에 1년이 되고 1월1일에 1년을 초과하므로 

2020년 1월1일에 1개를 추가 부여하여 줍니다. 이후 2년마다 연차가 하나씩 추가됩니다.

ex2)2017.01.15 입사자의 연차일이 16일이 되는 연도는?

2018.01 14.4개

2019.01 15개

2020.01 15개

2021.01 16개

1월1일이 아닌 중도입사자이므로 2018년 1월1일을 1년차로 하여 2019년에 1년 1월 1일에 1년이 초과되는 것으로 보아 2019년에서 2년이 지난 2021년 1월1일에 연차가 16개가 됩니다.

 

연차일수수식5

2-6에서 설명한 것을 수식으로 만들고 그 수식을

조건문과 조건이 참일시 실행할 수식으로 나누어 봤습니다.

 

 

1. (YEAR(TODAY())-YEAR(F8)=3,MONTH(F8)=1,DAY(F8)=1),

2. HLOOKUP(YEAR(TODAY())-YEAR(F8),$C$5:$K$6,2,1),

 

1번은 조건문입니다.

현재연도와 입사년도 차이가 3년이고 입사일이 1월1일이면, 

2번 참일때 실행 수식에서 HLOOKUP함수로 녹색배경셀C5:K6,범위에서 2번째 행에서 값을 찾아 넣습니다.

연차일수수식6
YEAR(TODAY())-YEAR(F8)=3,15의 결과값

 

YEAR(TODAY())YEAR(F8)=3,15,

차이가 3년이면 15일을 반환합니다.

 

위에서 1월1일인 경우에 연차가산일수를 구했으니 지금 수식에선 1월1일입사자가 아닌 경우에 연차일수를 구합니다.

 

 

연차일수수식7

 

YEAR(TODAY())YEAR(F8)>3,HLOOKUP(YEAR(TODAY())YEAR(F8),$C$5:$K$6,2,1),

3년보다 크다면 HLOOKUP함수로 C5:K6범위에서 근속연수에 따른

연차일수를 찾아 반환합니다. 

 

HLOOKUP함수 4번째 인수가 0이 아닌 1인 이유는

4년차, 6년차 등 짝수 연차에 따른 값을 찾기 위해 ‘정확히일치’가 아닌 유사일치로 찾습니다. 

찾으려는 값이 없는 경우 찾으려는 값보다 작은값을 반환하게 됩니다.

YEAR(TODAY())YEAR(F8)>=21,25)

당해년도에서 입사년도를 차감한 값이 21년 이상이면 25일을 반환합니다.

연차갯수는 25일이 최대입니다.

기본 연차일수가 15일이니 근로기준법상 최대 부여일수는 10일입니다.

이로써 연차갯수를 자동으로 구하는 방법에 대해서 알아보았습니다.

 

실무에서 많이 쓰이는 함수 DATEDIF 함수는 블로그내 사용법과 활용법이 있습니다.

링크 참고하시기 바랍니다.


DATEDIF함수 사용법

DATEDIF함수 활용법

 

다음 포스팅에서는 연차사용데이터를 통한 자동으로 연차사용관리하는 파일을 만들어보겠습니다.