엑셀 datedif 함수

[주황규팀장]

엑셀 datedif 함수

2010/11/29 18:01

형식 datedif(start_date,end_date,return_type)

return_type

y=연,m=월,d일수

Ym 날자간격을 무시한 월단위,md 날자간격을 년과 월을 무시한 일단위,

Yd 날자 간격을 년을 무시한 일단위

A1=1990-01-01 TODAY() =2010-10-10

예제 = datedif(a1,today(),"Y")

결과 20

안녕하세요?

오늘은 DATEDIF 함수에 대해서 알아 보겠습니다.

DATEDIF 함수는 함수목록에 나오지 않는 관계로 많은 분들이 그 사용법에 대한

의문을 가지고 계시는 것으로 알고 있습니다.

본론으로 들어가서...

사용방법은 간단해서 예를 먼저 보시는 것이 좋겠습니다.

시작일

종료일

기간

2001-01-03

2003-11-05

2

=DATEDIF(B12,C12,"Y")

2001-01-03

2003-11-05

34

=DATEDIF(B13,C13,"M")

2001-01-03

2003-11-05

1036

=DATEDIF(B14,C14,"D")

2001-01-03

2003-11-05

10

=DATEDIF(B15,C15,"YM")

2001-01-03

2003-11-05

306

=DATEDIF(B16,C16,"YD")

2001-01-03

2003-11-05

2

=DATEDIF(B17,C17,"MD")

어떤분들은 위 수식만 보시고도 이미 이 함수에 대해 통달(?) 하셨으리라 생각합니다.

그만큼 간단한 함수이므로 설명 또한 간단히 합니다.

그러나 날짜 계산이라는 것이 그리 간단한 것이 아니라는 것 또한 잊지 마세요.

이 강좌 끝부분에서 이 함수의 문제점에 대해서도 약간 설명을 달겠습니다.

=DATEDIF(시작일, 종료일, 단위)

DATEDIF 함수는 위와 같이 세가지 인수를 가집니다.

즉, 이 함수는 두 날짜 사이의 기간을 어떤단위로 계산할 것인지에 따라

그 결과를 알려주는 함수라 할 수 있습니다.

처음 두개의 인수는 너무나 간단해서 세번째 인수에 대한 것을 먼저 설명 드리도록 하겠습니다.

기간을 구분하는 단위는 세가지를 제공합니다.

년, 월, 일 즉 Y, M, D 이 세가지죠. (대소문자는 구분하지 않습니다.)

크게는 Y, M, D 세가지이나 이 세가지 단위를 조합하여 모두 여섯가지 단위로 표현할 수 있습니다.

단위

설명

기본

Y

두 날짜 사이에 몇년이 있는지를 구합니다.

나이를 예를들면 한국나이를 구하는 것이 아니라

서양식 나이 즉 만으로 나이를 구할 때 사용할 수 있습니다.

M

두 날짜 사이에 몇개월이 있는지를 구합니다.

30 개월, 60 개월 등으로 구해지므로

이자 계산등에 사용할 수 있습니다.

D

두 날짜 사이에 몇일이 있는지를 구합니다.

이 경우엔 단순히 두 날짜를 빼는 것과 같은 결과를 보여줍니다.

따라서 D 가 단독으로 사용될 일은 거의 없습니다.

조합

YM

두 날짜 사이에 완전한 연도를 제외한 개월수를 구합니다.

예) 35 개월로 반환하는 것이 아니라 35 개월은 2년 11개월이므로

앞의 단위 Y 에 해당하는 2년을 제외한 개월수 즉, 11 을 반환합니다.

YD

두 날짜 사이에 완전한 연도를 제외한 날짜수를 구합니다.

예) 두 날짜 사이에 400 일이 지났고 이것이 1년 하고 35일이라고 가정할 때

앞의 단위 Y 에 해당하는1년을 제외한 날짜 수 즉, 35 를 반환합니다.

MD

두 날짜 사이에 완전한 월을 제외한 날짜수를 구합니다.

예) 두 날짜 사이에 30개월 25일이 지났다고 가정할 때

앞의 단위 M 에 해당하는 30 을 제외한 날짜 수 즉 25 를 반환합니다.

여기까지 설명을 보셨으면 다시 위의 예제를 복사해서 설명 드립니다.

애초에 설명부터 할 것을 위에 뭐하러 예를 들었는지에 대해 불만을 가지실 혹시나 하는 분들을 위한 말씀. ^^

설명을 보기 전에 먼저 간단한 몇가지 예를 보고, 이 함수에 대해 생각해 보실 시간을 드리기 위함입니다.

마치 식스센스를 보고 있는데 누군가 브루스 윌리스가 유령이야 라고 소리친다면 얼마나 김이 빠지겠습니까...

아직 식스센스를 보지 않으신 분께는 죄송합니다. (__)

시작일

종료일

기간

2001-01-03

2003-11-05

2

=DATEDIF(B72,C72,"Y")

Y

2001-01-03

2003-11-05

34

=DATEDIF(B73,C73,"M")

M

2001-01-03

2003-11-05

1036

=DATEDIF(B74,C74,"D")

D

2001-01-03

2003-11-05

10

=DATEDIF(B75,C75,"YM")

YM

2001-01-03

2003-11-05

306

=DATEDIF(B76,C76,"YD")

YD

2001-01-03

2003-11-05

2

=DATEDIF(B77,C77,"MD")

MD

사실 설명은 위에서 이미 했지만... ^^;

예제를 가지고 다시 한번 설명 드립니다.

시작일부터 종료일까지의 기간을 표현하는 방법은 여러가지가 있습니다.

위에처럼 2001년 1월 3일 부터 2003년 11월 5일을 예를들면

기간은 단위와 정확도에 따라 다음과 같이 여러가지 형태로 표현 될 것입니다.

대충 말하는 경우

2년 쫌 넘는다

3년 쫌 안된다

1000 일 쫌 넘을걸

34개월쯤...

보다 정확하게 말하면...

2년 10개월 2일

34개월 2일

1036일

2년 306일

다행인지 불행인지...

컴퓨터는 대충을 모릅니다.

정확한 표현을 위 수식으로 다시 표현해 보겠습니다.

2001-01-03

2003-11-05

2년 10개월 2일

2년 10개월 2일

=DATEDIF(C100,D100,"Y") & "년 " & DATEDIF(C100,D100,"YM") & "개월 " & DATEDIF(C100,D100,"MD") & "일"

34개월 2일

34개월 2일

=DATEDIF(C100,D100,"M") & "개월 " & DATEDIF(C100,D100,"MD") & "일"

1036일

1036일

=DATEDIF(C100,D100,"D") & "일"

2년 306일

2년 306일

=DATEDIF(C100,D100,"Y") & "년 " & DATEDIF(C100,D100,"YD") & "일"

지난 강좌에서 설명드렸듯이...

1036 일로 표현하고 할 땐 단순히 두 날짜를 빼주어도 동일한 결과를 반환합니다.

1036일

=D100-C100 & "일"

2년 10개월 2일의 형태로 구하기 위해 DATEDIF 함수를 세번이나(?) 사용했습니다.

더 간단히 하는 방법은 없을까요? ^^

워크시트 함수만으로는 가능하지 않습니다. ㅠ.ㅠ

그러나 사용자 정의 함수를 만들어 워크시트에서 사용하시면 되는데요.

그와 같은 기능을 하는 함수를 이미 만든 사람들이 여럿 있습니다.

그중에 제가 자료실에 올린 MOREFUNCTION 이 있는데 한글을 지원하지 않습니다. ^^;

참고로...

대충말하는 경우에 대한 것도 규칙만 있다면 가능합니다.

2001-01-03

2003-11-05

2년 쫌 넘는다

2년 쫌 넘는다

=DATEDIF(C124,D124,"Y") & "년 쫌 넘는다"

이 수식만으로는 어딘가 부족합니다.

딱 2년인 경우엔 2년이 넘지 않음에도 불구하고...

게다가 쫌 넘는게 아니라 많이 넘는 것 같군요. ^^

많고 적음은 정하기 나름이므로... ^^

처음 두가지 인수에 대한 설명입니다.

우선 두 날짜 사이의 기간을 구하는 함수이므로

처음 두 인수는 날짜로 인식할 수 있는 값이나 참조가 들어와야 합니다.

시작일보다 종료일이 같거나 커야 합니다.

왜 이러해야 하는지는 설명이 필요없겠죠? ^^;

세번째 인수는 위 여섯가지 형태 중 하나라야 합니다.

덧붙여서 세번째 인수에 위 여섯가지를 제외한 형태가 나온다면...?

궁금하신 분은 직접 실험해 보시길... ^^

이 세가지 기본원칙을 어기면 오류를 반환합니다.

날짜로 인식할 수 없는 값이나 참조가 있을 경우엔 #Value 오류를

나머지 경우엔 #Num 오류를 반환합니다.

이 함수의 문제점...

DATEDIF 함수는 다른 스프레드시트 프로그램들과의 호환성을 위해 존재한다고 합니다.

그러기에 함수목록에도 없고 버전에 따라 도움말에도... ㅠ.ㅠ

다른 프로그램과의 호환성을 지키기 위함이라는 부분에 문제가 있습니다.

다른 프로그램과의 호환성을 지키기 위해...

그 다른 프로그램이 가지고 있는 버그까지 호환성을 유지하거든요. ㅋㅋ

다음 몇가지 예를 들어보겠습니다.

2001-02-28

2001-03-29

1

=DATEDIF(C162,D162,"M")

2001 년 2 월 28 일은 그달의 마지막 날짜 입니다.

3월 29일은 3월의 마지막 날짜가 아니지만 완전한 1 개월이 지났다고 보여줍니다.

실제 상황에서라면 3월 31 일을 1 개월이 되는 시점으로 봐야 할 것입니다.

2001-01-30

2001-02-28

0

=DATEDIF(C168,D168,"M")

1월 30 일은 1월의 마지막 날짜도 아니죠.

2월 28 일은 그해 2월의 마지막 날짜입니다.

그렇다면 실제계산에선 0 이 아닌 1 이 나와야 할 것입니다.

모든 경우에 대해 정확한 날짜 계산을 하는 것은 간단한 문제가 아닙니다.

정확한 날짜 계산을 위한 기준부터 정하는 것이 순서일텐데요.

보통은 민법에서 정한 역(歷) 계산법을 따르는 것이 일반적입니다.

그러나 이 방법을 워크시트 함수로 간단히 구현 하기엔 문제점이 아주 많습니다.

사용자 정의 함수를 만들어 해결하는 것이 거의 유일한 해결책입니다.

나중에 민법책을 보게 되면 만들어 볼까요? ㅋㅋ

다른 프로그램과의 호환성 때문에 버그까지 물려 받은 경우는 또 다른 좋은 예가 있습니다.

엑셀 이전에 한 시대를 풍미했던 로터스에...

다음과 같은 버그가 있습니다. 1900 년 2 월 29 일이 존재하느냐 안하느냐...

즉 1900 년 2 월은 윤년인지 아닌지를 알아야겠죠.

아시다시피 윤년이 아닙니다.

따라서 1900 년 2 월 29 일은 존재하지 않습니다.

그러나 엑셀에선 날짜체계를 그대로 물려받아 이 버그까지 따라하고 있답니다. ^^

1900-02-29

보시다시피 올바른 날짜로 인식되죠.

1901-2-29

만약 날짜로 인식되지 않았다면 아래의 예처럼 왼쪽으로 정렬되어

문자열로 인식 되었어야 하겠죠.

결국 엑셀에선 이러한 버그를 피해가기 위해 1904 년 날짜 체계를 지원합니다.

1904 년 체계라는 것은 날짜의 시작이 1900 년이 아니라 1904 년이라는 것이죠.

하필 1904 년일까 하는 의문을 품으신다면...

1900 년 이후의 첫 윤년이기 때문이 아닐까 하는 생각입니다.

1904 년 날짜 체계를 사용하시려면 옵션에서 설정하실 수 있습니다.

Bitmap Bitmap

엑셀의 최초버전은 아시다시피 윈도우가 아니라 맥킨토시용으로 먼저 개발 되었습니다.

윈도우용 엑셀은 1900 년 체계를 기본으로 사용하나 맥킨토시용 엑셀은

1904 년 체계를 기본으로 사용합니다.

도움말의 다음 내용을 참고하세요.

날짜 체계

처음 날짜

마지막 날짜

1900

1900-01-01

9999-12-31

(일련 번호 1)

(일련 번호 2958465)

1904

1904-01-02

9999-12-31

(일련 번호 1)

(일련 번호 2957003

결론...

DATEDIF 함수에 대해 알아보았는데요.

개인적인 생각에 좋은점 보다는 그렇지 않은 부분이 더 많아 보입니다.

아마도 그러한 이유때문에 함수목록이나 도움말에도 실리지 않는 것이 아닌지... ^^;

정확한 기간 계산이 힘든 이유는

달력이라는 것 자체가 인위적으로 만들어졌기 때문이고

실제 적용하는 방법 역시 나라마다, 시대마다, 분야마다 다르기 때문입니다.

정확한 기간 계산을 하시려면

정확한 규칙을 먼저 알아야겠죠. ^^

암튼 오늘 강좌는 여기서 마칩니다. (__)

모쪼록...

2001-03-31

같이 생각해 봅시다

2001-01-15⇒2001-01-31

2001-02-15⇒2001-02-28

2001-03-20⇒2001-03-30

이렇게 말일을 표시하고자 한다면

2001-01-15⇒=EOMONTH("2001-01-15",0)하면 2001/1/31

2001-02-15⇒=EOMONTH("2001-02-15",0)하면 2001/2/28

2001-03-20⇒=EOMONTH("2001-03-20",0)하면 2001/3/30

할수도 있다는 말씀입니다

EOMONTH함수는 추가기능 분석에서 가능합니다

--------------------- [원본 메세지] ---------------------

왜 그런지 알 수 없지만 굳이 표현하자면 다음과 같습니다

다음은 2001년 11월 30일을 표현한것입니다

1905-01-30

2001년 12월 31일을 표현하시려면 11->12, 30->31로 바꾸시면 됩니다

이렇게 한후 셀서식을 날짜 서식을 yyyy-mm-dd로 바꾸시면

2001-12-31로 나타납니다...

--------------------- [원본 메세지] ---------------------

저기여~~

정말 한참 초보라서..

어떻게 질문을 해야할지..

대충 잘 이해해주세여...

회사에서 내준 숙제거든요...

한셀에다가 2001-01-31..이렇게 하구 블럭잡아서

월채우기..맞나? 암튼 그렇게하면

2001-01-31

2001-02-28

2001-03-31

2001-04-30

.

.

.

.

암튼 그달에 마지막날 맞춰서 채워지잖아요..

근데 이걸 함수를 써서 똑같이 해오라네요~~

year,month,day,date..그담엔 기억안나네요..암튼

이것들을 사용하라는데..

이걸 어째야하는거죠???

아우~~

엑셀공부 잼있긴한데..

가끔 이런 곤란한 질문들 땜시 죽겠네여~~

리플 부탁드려여~~