엑셀 사용 만료일 및 잔여 연수 계산하는 방법

직장에서 업무상 라이센스 종료일, 사내 사무기기의 교체일, 정년퇴직일 등 만료일을 계산하고 잔여 연수를 표시해야 할 때, 종종 엑셀을 사용해서 관리하는 경우가 많습니다. 그럴 때, 이제 EDATE 함수와 YEARFRAC 함수를 사용해보세요.

그밖에 품질보증기한과 유통기한, 자격증의 유효기간을 계산할 때에도 유용하게 사용할 수 있습니다!👍

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

✅ 사용 만료일 구하는 공식

=EDATE(기준일,만료기간(개월))

① 기준일 : 만료일 계산을 위한 기준일(시작날짜) 입니다.
② 만료기간 : 만료되는 기간을 개월수로 입력합니다.

✅ 오늘 기준 잔여연수 계산 공식

=YEARFRAC(만료일,TODAY())

① 만료일 : 오늘 기준 잔여연수를 구할 만료일입니다.
- 📑 YEARFRAC 함수는 시작,종료일 구분없이 두 날짜의 차이를 계산하므로 =YEARFRAC(TODAY(),만료일)로 바꾸어 입력해도 동일하게 동작합니다.

EDATE 함수는 지정된 날짜로 부터 특정 개월수 전/후의 날짜를 계산합니다.

=EDATE(①시작일, ②개월수)

EDATE 함수는 월별 날짜수(28일, 30일, 31일)에 상관없이 시작일을 기준으로 몇 개월 전/후로 계산할 수 있어 편리합니다.
① 시작일: 표시형식이 '날짜'로 지정되어있어야 합니다.
② 개월수: 시작일 기준으로 이전 날짜를 계산할 때는 음수로, 이후 날짜 계산은 양수로 입력합니다.
= EDATE("2022-01-15",0) '2022-01-15를 반환합니다.
= EDATE("2022-01-15",1) '1개월 후인 2022-02-15를 반환합니다.
= EDATE("2022-01-15",-1) '1개월 전인 2021-12-15를 반환합니다.

YEARFRAC 함수는 두 날짜 간의 차이가 일년 중 차지하는 비율을 계산합니다.

=YEARFRAC(①시작일, ②종료일, ③[계산기준])

YEARFRAC 함수를 사용하면 두 날짜 사이의 1년 단위 백분율 진행상황을 계산할 수 있습니다. 백분율은 무조건 양수로만 계산됩니다.
① 시작일, ②종료일 : 모두 올바른 날짜 형태로 입력합니다.
③계산기준: [선택인수] 아무것도 입력하지 않으면 기본값인 1년을 360일, 한달을 30일로 가정하여 계산합니다.
=YEARFRAC("2021-01-01","2021-07-01) '두 날짜 차이(181일)의 1년 중 비율인 50%를 반환합니다.
=YEARFRAC("2021-10-01","2022-03-15") '두 날짜 차이(165일)의 1년 중 비율인 45.6% 를 반환합니다.

이번 예제에서는 사내 사무기기의 사용만료일(교체일)과 잔여 연수를 계산해보겠습니다.

아래 표에 구입일자(C열)와 사내 규정에 따른 사용연수(D열)를 입력합니다. 구입일자와 사용만료일의 표시형식을 '날짜'로 변경합니다.
표시형식을 날짜로 변경하기
사용만료일을 계산하기 위해 E3셀에 =EDATE(C3,12*D3)을 입력합니다. EDATE함수는 입력한 시작일로부터 몇 개월 전/후의 날짜를 계산합니다. 예제에서는 단위로 '년도'를 사용하였으므로 개월수로 변경하기 위해 사용 연수(D3셀)에 12를 곱해서 계산합니다.
사용 만료일 계산 - EDATE함수
마지막으로 YEARFRAC함수를 사용하여 잔여 연수를 계산합니다. 시작일과 종료일에 각각 사용만료일과 오늘 날짜를 아래와 같이 입력합니다.
=YEARFRAC(만료일,TODAY())

YEARFRAC 함수를 사용해 잔여연수를 계산합니다.
마지막으로 입력한 공식을 마우스로 드래그하여 자동채우기하면 사용 만료일과 잔여연수 계산이 완성됩니다.
입력한 수식을 자동채우기하여 만료일과 잔여연수 계산을 마무리합니다.

5 4 투표

게시글평점