엑셀 인사/총무를 위한 날짜 함수 총정리! [실전예제 6개 포함]

실무에서 사용되는 다양한 엑셀 날짜함수의 사용법을 실전 예제와 함께 하나씩 단계별로 살펴봅니다.

홈페이지 » 엑셀 인사/총무를 위한 날짜 함수 총정리! [실전예제 6개 포함]

엑셀 날짜함수 총정리! 엑셀 날짜관련 실전예제 6개 :: 오빠두엑셀 함수 2-1강

예제파일
첨부파일에 이상이 생겼을 경우, 1:1 문의하기로 연락주시면 신속히 해결해드리겠습니다. 

관련강의 살펴보기

엑셀 달력 공휴일 자동 달력 만들기 썸네일크기
엑셀 달력, 공휴일 및 일정 관리 자동달력/만년달력 만들기
엑셀 달력 목차 바로가기 영상강의 예제파일 다운로드 엑셀 달력 자동화양식 다운로드 링크 엑셀 달력 제작을 위한 날짜계산 함수 공식 달력에 ...
엑셀 FILTER 함수 필수 강의 썸네일3크기
엑셀 FILTER 함수 실전 사용법 및 문제해결, 총정리!
FILTER 함수 실전예제 목차 바로가기 예제파일 다운로드 오피스 365 사용자가 아니라면? xFILTER 추가기능 알아보기 FILTER 함수 기초 사용법 셀을 참조하여 ...

관련 기초내용 포스트 | by 오빠두엑셀

EOMONTH 함수 사용법
엑셀 EOMONTH 함수 사용법 및 실전예제 :: 날짜 함수
실습파일 EOMONTH 함수는 시작일로부터 특정 개월수 이후의 마지막날을 반환하는 함수입니다. EOMONTH함수는 특정날짜로부터 지정한 개월 수 이전/이후의 달의 마지막날을 반환합니다. EOMONTH ...
EDATE 함수 사용법
엑셀 EDATE 함수 사용법 및 실전예제 :: 날짜 함수
실습파일 EDATE 함수는 지정한 날짜로부터 특정 개월수 전/후의 날짜를 계산합니다. EDATE함수는 지정한 날짜로부터 특정 개월수가 지난 전/후의 날을 계산하는 함수입니다 ...

1. 입력된 날짜에서 연도/월/일/요일 따로 분리하기

직원별로 생년월일 정보를 받았습니다. 직원별로 나이를 계산하고 각 직원의 월별 생일정보를 취합한다고 가정해 보겠습니다. 이럴 경우, YEAR 함수, MONTH 함수, DAY 함수를 사용할 수 있습니다.

YEAR 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 선택된 날짜의 년도를 반환합니다.
구문 : = YEAR ( 날짜 )
MONTH 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 선택된 날짜의 월을 반환합니다.
구문 : = MONTH ( 날짜 )
DAY 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 선택된 날짜의 일을 반환합니다.
구문 : = DAY ( 날짜 )
날짜에서 월 추출하기
각 직원의 생년월일에서 년도, 월, 일의 값을 따로 분리합니다

또한 반대로, 각 날짜의 연도/월/일로 입력된 값을 날짜로 변환해야 할 때에는 DATE 함수를 사용합니다. 이러한 경우는 보통 한번 가공된 자료에서 종종 발생합니다. 예를 들어, 년도별(2017, 2018..) 또는 월별(1,2,3..) 로 열이 구분되어 있고 이러한 값을 2017/1/1, 2017/2/1.. 의 날짜로 변환할 때 사용합니다.

DATE 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 각 입력된 연도, 월, 일을 날짜로 변환합니다. 
구문 : = DATE ( 연도, 월, 일 )

만약 해당 날짜의 요일을 출력해야 할 땐 어떻게 할까요? 크게 2가지 방법을 사용할 수 있습니다.

a. 사용자 지정서식 활용 (대부분의 상황에서 사용)

사용자 지정서식을 활용할 경우, 실제 셀 안에 입력된 값은 날짜(숫자)이고 겉으로 표시되는 값만 요일로 변경됩니다. 따라서 사용자 지정서식을 활용할 경우, 해당 날짜 셀을 바로 참조하여 계산가능한 장점이 있습니다.

따라서 일반적인 상황에서는 사용자 지정서식을 활용하는 것을 권장합니다.

날짜에서 요일 추출하기
셀 안에는 날짜가 입력되었지만, 사용자 지정서식을 변경하여 원하는 형식으로 요일을 동시에 출력할 수 있습니다.

b. TEXT 함수 활용

다만, 특정 상황에서는 TEXT함수가 용이할 수 있습니다. 사용자 지정서식으로 요일이 표시된 값을 복사해서 다른 셀에 값만 붙여넣기 해보겠습니다. 그럼 요일(텍스트형식)이 아닌 날짜(숫자형식)로 값이 붙여넣기 됩니다.

따라서, 각 날짜의 요일에 해당하는 ‘텍스트값’이 필요할 경우 TEXT 함수를 사용합니다.

TEXT 함수:: 텍스트함수 [상세설명 및 예제 보러가기]
설명 : 선택된 셀의 데이터(주로 숫자)를 원하는 형식으로 텍스트화 합니다.
구문 : = TEXT ( 변환할 값, "변환할 형식" )

만약 각 날짜의 요일별로 숫자를 불러와 계산이 필요할 때에는 어떻게 할까요? 예를 들어 월요일은 해당 값에 +1, 화요일은 +2 등.. 이런 식의 계산이 필요한 경우에는 WEEKDAY 함수를 사용합니다.

WEEKDAY 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 날짜에 해당하는 요일을 사용자가 지정한 유형에 따라 숫자로 반환합니다. 
구문 : = WEEKDAY ( 날짜, 변환할 유형 )

이전에 WEEKDAY 함수를 응용하여 자동화 달력 만들기 강의를 올려드렸습니다. 내용이 궁금하신 분은 관련 포스트를 참고하세요.

엑셀 달력 공휴일 자동 달력 만들기 썸네일크기
엑셀 달력, 공휴일 및 일정 관리 자동달력/만년달력 만들기
엑셀 달력 목차 바로가기 영상강의 예제파일 다운로드 엑셀 달력 자동화양식 다운로드 링크 엑셀 달력 제작을 위한 날짜계산 함수 공식 달력에 ...

2. 텍스트를 날짜로 변경하기

여러 부서 또는 여러 사람으로부터 자료를 취합하다 보면, 분명히 날짜로 보이는 값인데 날짜 계산이 안되거나 함수 결과값으로 #VALUE! 오류가 출력되는 경우가 생깁니다.

그 원인은 바로 텍스트 형식으로 입력된 날짜 때문인데요. 텍스트형식의 날짜를 일반 날짜형식으로 한번에 쉽게 변환하는 방법을 알아보겠습니다.

1. 날짜형식으로 변환하고자 하는 범위를 선택한 후, [데이터] – [텍스트 나누기] – [구분 기호로 분리됨] 을 선택한 후 [다음]을 눌러 2단계로 이동합니다.

텍스트 날짜 변환 텍스트 나누기
[데이터] – [텍스트 나누기] 기능을 이용하여 텍스트로 입력된 날짜를 손쉽게 변경할 수 있습니다.
2. 2단계에서는 바로 [다음]을 눌러 3단계로 이동합니다.

3. ‘열 데이터 서식’ 에서 ‘날짜’를 [년월일]로 선택한 후 [마침]을 눌러 날짜형식 변환을 마무리합니다.

날짜 연도월일 나누기
[날짜]에서 [년월일]을 선택한 후 [마침]을 눌러 날짜변환을 완료합니다.
4. [텍스트 나누기] 기능을 활용해도 날짜형식으로 변환되지 않는 텍스트 형식의 날짜가 있을 수 있습니다. (특히 년/월/일로 나뉘어져 입력된 날짜의 경우가 그렇습니다.) 그럴 경우 아래 수식을 복사한 뒤 우선 아무 셀에 수식을 붙여넣기 합니다. 수식을 붙여넣기 하면 #NAME? 오류가 나옵니다. 바로 다음단계로 넘어갑니다.

=DATE(LEFT(셀,FIND("년",셀,1)-1),MID(셀,FIND("년",셀,1)+1,FIND("월",셀,1)-FIND("년",셀,1)-1),MID(셀,FIND("월",셀,1)+1,LEN(셀)-FIND("월",셀,1)-1))

5. 수식을 붙여넣기 한 셀을 선택한 뒤, 키보드 CTRL + H 키를 눌러 [찾기 및 바꾸기] 창을 불러옵니다.

  • 찾을 내용 : 셀
  • 바꿀 내용 : B8 (텍스트로 입력된 날짜의 셀 주소)
  • 찾는 위치 : 수식 ⇨  [모두 바꾸기] 버튼 클릭

엑셀 찾기 및 바꾸기
[찾기 및 바꾸기] 기능을 활용하여 수식의 ‘셀’ 로 입력된 텍스트를 해당 셀주소로 한번에 변경합니다.
6. 안내메세지가 뜨면서 해당 수식의 결과값으로 올바른 형식의 날짜가 반환됩니다.

엑셀 찾기및 바꾸기
[안내메세지]가 뜨면서 올바른 형태의 날짜가 수식의 결과값으로 반환됩니다.

3. 각 소모품의 교체주기 별 교체일 계산 및 경고표시 띄우기

총무부에서 일하게 되면 소모품 또는 라이선스 등의 갱신일을 수시로 점검해야 합니다.

특히 여러 개의 매장을 관리중인 경우 또는 개개인의 별도 프로그램 라이선스를 발급받아 사용 중일 경우에는 각 항목별 납기일을 수시로 챙겨줘야 하므로 실무자의 지속적인 관리가 필요합니다.

엑셀 갱신일 라이선스 관리
제품 또는 라이선스의 갱신일 관리는 총무과에서 지속적으로 관리해야 하는 업무 프로세스 중 하나입니다.

만약 엑셀로 해당 자료를 관리할 경우, EDATE 함수를 사용하면 각 항목의 교체주기에 따라 다음 교체일을 자동 계산할 수 있습니다. 또한 TODAY 함수와 같이 응용하면 각 제품별 교체일이 지났을 경우, 또는 교체일이 다가올 시 해당 셀에 경고표시를 띄워 담당자의 실수를 사전에 방지할 수 있습니다.

EDATE 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 지정한 날짜로부터 특정개월 전/후의 날짜를 계산하여 반환합니다.
구문 : = EDATE ( 시작일, 개월수 )
TODAY 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 사용 중인 PC의 현재 날짜를 반환합니다. 
구문 : = TODAY ()

4. 직원별 수습기간 및 최초 연봉협상일 계산하기

지정한 날로부터 몇 개월이 지난 달의 마지막날을 계산하려면 어떻게 할까요? 대부분의 급여 또는 대금 결산은 매월 말일을 기준으로 계산되므로, 이러한 월말 기준의 날짜계산은 인사 및 총무 업무에서 자주 사용됩니다.

이를 위해서 EOMONTH 함수를 사용합니다.

EOMONTH 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 지정한 날짜로부터 특정개월이 지난 월의 마지막날을 반환합니다. 
구문 : = EOMONTH ( 시작일, 개월수 )

이번 강의에서는 각 직원의 입사일 기준으로 3개월이 지난 뒤의 수습기간 종료일을 계산합니다. 각 수습기간은 3개월이 지난 달의 마지막 날 종료된다고 가정하였습니다.

직원별 수습기간 계산
입사일을 기준으로 수습기간(3개월)이 지난 달의 마지막날을 계산합니다.

보통 수습기간에는 연봉협상이 이루어지지 않으므로, 수습기간이 끝난 뒤의 최초 연봉협상일을 계산합니다. 이번 예제에서는 매년 4월 연봉협상을 한다고 가정합니다. 따라서, 수습기간 종료일이 4월 이전일 경우 같은 해 4월 1일날 연봉협상을 하며, 그렇지 않을 경우 다음해의 4월 1일날 연봉협상을 하도록 수식을 입력합니다.

IF 함수:: 논리함수 [상세설명 및 예제 보러가기]
설명 : 논리식을 판단하여 참일 경우와 거짓일 경우의 결과값을 구분하여 반환합니다. 
구문 : = IF ( 논리식, 결과값[참일경우], 결과값[거짓일경우] )
엑셀 직원별 연봉협상일 계산
수습기간이 끝나는 월을 비교하여, 4월 이전일 경우 같은 해, 4월 이후일 경우 다음 해에 연봉협상을 합니다.

5. 특정요일, 공휴일 제외한 실제 근무일수 계산하기

대부분의 일용직, 아르바이트 직원의 급여는 실제 근무일수를 기준으로 계산합니다. 따라서 정규직처럼 정해진 주말과 공휴일에만 쉬는 것이 아닌, 특정 요일(월/수/금)에 쉬는 경우에는 별도의 계산식을 넣어줘야 합니다. 뿐만 아니라 별도의 공휴일도 같이 고려하여 계산식을 세울 경우에도 하나의 함수로 쉽게 해결할 수 있습니다.

이를 위해 NETWORKDAYS.INTL 함수를 사용합니다. NETWORKDAYS.INTL 함수는 엑셀 2010 이후 버전에서 사용가능합니다.

NETWORKDAYS.INTL 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 특정 요일 또는 공휴일을 별도로 지정하여, 해당 날짜를 제외한 두 날짜 사이의 실제 근무일수를 계산합니다. 
구문 : = NETWORKDAYS.INTL ( 시작일, 종료일, [요일별 휴무], [공휴일] )

엑셀 공휴일 제외 실제 근무일 계산
[근무일선택] 입력으로 반환된 7자리 텍스트를 [요일별 휴무]로 입력하여, 공휴일과 요일별 휴무를 제외한 실제 근무일수를 계산합니다.
공휴일 범위는 매번 추가되는 공휴일을 새로 입력할 시 자동으로 범위가 수식에 반영될 수 있도록 OFFSET 동적범위를 사용합니다. OFFSET 동적범위에 대한 자세한 설명은 관련 포스트를 참고하세요.
엑셀 동적범위 기초부터 응용 썸네일크기
엑셀 동적범위 공식 및 사용법 총정리 :: OFFSET 동적범위
엑셀 동적범위 목차 바로가기 영상강의 예제파일 다운로드 엑셀 동적범위란? 함수 기초(OFFSET 함수, COUNTA 함수) OFFSET 함수 동적범위 만들기 동적범위 실전예제 ...

예제파일에서 키보드 CTRL + F3키를 눌러 이름관리자를 실행한 뒤, 아래 수식으로 새로운 사용자지정범위를 생성합니다.

=OFFSET('근무일계산(주말or휴일선택제외)'!$M$8,,,COUNTA('근무일계산(주말or휴일선택제외)'!$M$8:$M$1000))
엑셀 사용자 지정범위 만들기
OFFSET 동적범위로 새로운 사용자지정범위를 추가합니다.

6. 직원별 근속년수 또는 근속개월수 계산하기

직원별 근속년수와 근속개월수를 계산합니다. YEAR 함수와 MONTH 함수를 응용하여 계산할 수도 있지만, DATEDIF 함수를 사용하면 하나의 함수로 더욱 간단히 계산할 수 있습니다.

DATEDIF 함수:: 날짜함수 [상세설명 및 예제 보러가기]
설명 : 두 날짜사이의 일,월,연도 차이를 다양한 방법으로 계산합니다. 
구문 : = DATEDIF ( 시작일, 종료일, 계산단위 )
엑셀 근속년수 계산
입사일(시작일)과 기준일(종료일) 사이의 연도수(근속년수)를 계산합니다.

강의가 도움 되셨으면 좋아요와 댓글 부탁드려요! 여러분의 소중한 댓글은 강의제작에 큰 힘이 됩니다.
엑셀 공부는 오빠두엑셀과 함께하세요! #엑셀은오빠두

4.7 3 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
3 Comments
Inline Feedbacks
View all comments
크리슈
크리슈
2020년 3월 29일 2:57 오후
게시글평점 :
     

항상 많이 배워갑니다!

고재봉
고재봉
2020년 4월 6일 9:58 오후
게시글평점 :
     

항상 감사합니다.
업무고민이 해결 될거 같음...

엑린이
엑린이
2020년 5월 30일 8:55 오후
게시글평점 :
     

감사합니다

3
0
여러분의 생각을 댓글로 남겨주세요.x