들어가며
"엑셀에서 날짜를 계산할 수 있나요? 예를 들어 오늘 날짜에서 100일 이전 날짜를 엑셀로 어떻게 알아내죠?"
이런 질문을 받을 때가 있습니다. 당연히(!) 가능합니다. 엑셀의 계산 엔진은 날짜나 시간을 숫자로 관리하기 때문입니다. 이번 시간에 소개하는 내용을 이해하면 주민등록번호에서 나이를 추출하고, 사원명부에서 근속년수를 계산하고, 잔업시간을 계산하는 등 날짜나 시간과 관련된 각종 계산을 자유롭게 할 수 있게 됩니다. 기대되시죠? 출발합니다.
엑셀이 날짜와 시간을 관리하는 방법
엑셀은 날짜나 시간 개념이 따로 없습니다. 엑셀의 계산 엔진은 모든 날짜와 시간에 대해 일련번호를 부여하여 관리합니다. 날짜는 정수로, 시간은 0과 1 사이의 소수로 관리합니다.
1900년 1월 1일은 '1', 1900년 1월 2일은 '2', 1900년 1월 3일은 '3'에 해당됩니다. 이처럼 엑셀은 날짜에는 정수의 일련번호를 붙여서 관리합니다. 같은 날짜라 하더라도 다양한 형식을 지정해서 다르게 표현할 수 있습니다.
엑셀은 날짜와 시간을 숫자로 관리하되, 정수 부분은 날짜, 소수 부분은 시간을 나타냅니다. 날짜나 시간 데이터를 이용하여 연산을 할 수 있는 것은 그래서입니다.
오늘 날짜와 현재 시간을 입력하는 2가지 방법
셀에 날짜를 입력할 때에는 몇 가지 형식으로 입력할 수 있습니다. '년도-월-일' 또는 '년도/월/일' 등이 대표적입니다. "년도.월.일"과 같은 형태로는 입력하지 않는 것이 좋습니다. 마침표로 년월일을 구분하면 문자열로 인식하여 계산을 하기 어렵습니다(불가능한 것은 아닙니다).
셀에 오늘 날짜나 현재 시간을 입력하는 경우가 흔히 있습니다. 셀을 선택하고 날짜를 직접 입력해도 되지만(예: 2024/4/2) Ctrl + ;(세미콜론) 단축키를 이용하면 편리합니다. Ctrl + :(콜론)을 사용하면 현재 시간이 자동으로 입력됩니다. 날짜나 시간을 직접 입력하거나 단축키로 입력한 경우, 그 값은 바뀌지 않습니다.
반면 함수를 이용하여 입력한 날짜(TODAY)나 시간(NOW)은 실시간으로 변합니다. 이 두 함수는 괄호 안에 아무 인수 없이 함수 이름만 입력하면 됩니다.
간단한 예제를 하나 보고 넘어가죠. C19 셀에는 TODAY 함수를 사용한 현재 날짜가 입력되어 있습니다. D열에 경과일자를 구하고자 합니다. D21 셀의 수식을 보세요. 두 날짜를 빼주면 경과일자가 구해집니다. 날짜도 숫자이기 때문에 사칙연산을 적용할 수 있는 겁니다.
기준일자, 즉 C19 셀 값은 수식을 아래로 복사했을 때 변하면 안 되므로 절대 주소 형태로 지정한 점만 주의하시면 되겠습니다.
날짜 함수
YEAR, MONTH, DAY 함수
날짜와 관련된 대표적인 함수로 YEAR, MONTH, DAY가 있습니다. 단어에서 유추할 수 있듯이 날짜 데이터로부터 년도, 월, 일을 각각 구해주는 함수입니다.
=YEAR(serial_number)
=MONTH(serial_number)
=DAY(serial_number)
serial_number는 엑셀에서 사용되는 유효한 날짜 데이터를 의미하며, 몇 가지 방식으로 사용할 수 있습니다. YEAR 함수를 예로 들면 다음과 같습니다.
- [방법 1] 실제 날짜 이용: =YEAR("2024/3/31")
- [방법 2] 함수 이용: =YEAR(TODAY())
- [방법 3] 셀 참조하기: =YEAR(D34)
DATE 함수
DATE는 연도, 월, 일에 해당하는 인수값을 전달 받아서 날짜를 만들어주는 함수입니다.
=DATE(year, month, day)
' year: 년도를 나타내며, 1900에서 9999 사이의 숫자(정수)
' month: 월을 나타내며, 1에서 12 사이의 숫자(정수)
' day: 일을 나타내며, 1에서 31 사이의 숫자(정수)
예를 들어, 특정한 날짜를 생성하려면 다음과 같이 할 수 있습니다.
=DATE(2024,3,31)
[참고] 열 너비 변경 없이 날짜 제대로 표시하기
[활용 1] 연도, 월, 일 구하기
날짜 데이터에서 연도, 월, 일을 구하려면 다음과 같이 합니다. 유효한 날짜 데이터가 입력되어 있다면 쉽게 구할 수 있습니다.
[활용 2] 나이 계산하기
YEAR 함수를 알고 있으면 나이를 쉽게 구할 수 있습니다. 나이는 '현재 년도 - 생년 + 1'로 구한다고 가정합니다. D43 셀에는 다음과 같은 수식이 들어 있습니다.
=YEAR($C$41)-YEAR(C43)+1
현재 날짜는 C41 셀에 들어 있습니다. YEAR 함수를 사용하여 년도를 추출합니다. 앞에서 소개한 방법 중에서 [방법 3]이 적용되었군요. 나이는 '현재 년도 - 생년 + 1'로 구한다고 했으므로 그대로 수식을 작성하면 되겠습니다. 다만, 현재 년도는 수식을 복사했을 때 셀 주소가 고정되어야 하므로 절대 주소 형태로 지정한 점에 유의하세요.
[활용 3] 주민등록번호에서 생년월일 구하기
(1) 생년월일을 구할 D59 셀을 선택합니다.
(2) =date()를 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입]을 클릭합니다.
(3) [함수 인수] 대화상자에서 각 인수를 지정합니다. 주민등록번호 앞 6자리가 년/월/일이므로 문자열 함수를 이용하여 다음과 같이 하면 되겠습니다. 참고로, 대화상자의 각 인수를 지정하면 수식 입력줄의 수식은 자동으로 작성됩니다.
(4) [확인]을 클릭하면 생년월일이 구해집니다. 채우기 핸들을 이용하여 나머지 셀에도 수식을 채워넣기 하여 완성합니다.
두 날짜 사이의 경과 기간 계산하기
DATEDIF 함수 기본
날짜 관련 함수 중에서 DATEDIF라는 재미있는 함수가 있습니다. 이 함수는 두 날짜 사이의 경과 년/월/일을 구해주며, 엑셀 도움말에 나오지 않는 함수 중 하나입니다. 함수 인수 대화상자의 도움을 받을 수 없으므로 직접 입력해야 합니다.
=DATEDIF(시작일자, 종료일자, 연산방법)
' 연산방법에는 다음과 같은 종류가 있습니다.
' y: 두 날짜 사이의 경과년수
' m: 두 날짜 사이의 경과월수
' d: 두 날짜 사이의 경과일수
' ym: 두 날짜 사이에서 년수를 제외한 월수
' md: 두 날짜 사이에서 개월수를 제외한 일수
' yd: 두 날짜 사이에서 년수를 제외한 일수
(1) 만 나이를 구할 셀을 선택합니다.
(2) =DATEDIF(C74,$C$72,"y")를 입력하고 Enter를 누릅니다. '시작일자'보다 '종료일자'가 커야 한다는 점과, 수식을 아래로 복사했을 때 현재 날짜가 있는 셀 주소(여기서는 C72 셀)는 바뀌면 안 되므로 절대 주소로 지정한 점에 유의하세요.
(3) 수식을 아래로 복사하여 완성합니다(장수 마을인가... 100세 이상인 분이 많군요. 결과를 도출하기 위한 과정에 집중해서 보세요).
경과 기간 구하기
DATEDIF 함수를 이용하면 만 나이뿐만 아니라 경과 기간을 구할 수도 있습니다. E74 셀에 사용된 수식은 다음과 같습니다. 언뜻보니 아주 길고 무시무시해 보입니다.
=DATEDIF(C74,$C$72,"y")&"년 "&DATEDIF(C74,$C$72,"ym")&"개월 "&DATEDIF(C74,$C$72,"md")&"일"
하지만 찬찬히 뜯어보면 DATEDIF 함수를 세 번 반복해서 사용해서 복잡해 보이는 것일 뿐, 실제로 복잡한 것은 아닙니다('복잡해 보이는 것'과 '복잡한 것'은 엄연히 차이가 있습니다.ㅎㅎ).
DATEDIF 함수의 '연산방법' 인수 중에서 'ym'은 두 기간 사이의 월수를 구하되 년도를 무시하고, 'md'는 두 기간 사이의 날수를 구하되 월을 무시합니다. 수식을 분해해서 살펴보시면 이해하기가 좀 더 편하리라 생각합니다.
그 밖에 알아두면 좋은 몇 가지 함수
현재 상태에서 우리가 익혀야 할 날짜/시간 함수는 이 정도면 되겠습니다. 여력이 되는 분들은 다음과 같은 함수에 대해서도 검색 엔진이나 도움말을 참고로 정리해 두시기 바랍니다.
- WEEKDAY: 지정한 날짜에 해당하는 요일을 표시합니다. 기본적으로 요일은 1(일요일)에서 7(토요일)까지의 정수 형태로 표시됩니다(사용법: WEEKDAY(날짜, 반환 값 종류)).
- WORKDAY: 시작날짜와 경과일수 사이에서 주말이나 휴일을 제외한 날짜수, 즉 평일수를 구해줍니다(사용법: WORKDAY(시작일자, 경과일수, [휴일]). [ ] 안은 생략 가능.
- NETWORKDAYS: 시작날짜와 마지막날짜 사이의 전체 작업일수를 구합니다(사용법: NETWORKDAYS(시작날짜, 마지막날짜, [휴일]). [ ] 안은 생략 가능.
나가며
엑셀은 날짜와 시간을 숫자(일련번호)로 관리하며, 날짜와 시간을 다루는 다양한 함수에 대해 알게 되었습니다. 축하합니다. 이제 함수도 서서히 끝이 보입니다.
'Excel' 카테고리의 다른 글
| Excel에서 방향키가 작동하지 않을 때 대처 방법 3가지 (11) | 2024.04.04 |
|---|---|
| 2024년 현재까지 Excel에 추가된 모든 새로운 기능 정리 (59) | 2024.04.03 |
| Excel이 숫자를 날짜로, 날짜를 숫자로 변경하는 경우 대처 방법 (7) | 2024.04.01 |
| 여러 기준으로 Excel FILTER 함수를 사용하는 방법 (9) | 2024.03.29 |
| Excel에서 간트 차트를 만드는 방법: 단계별 가이드 (9) | 2024.03.27 |
