엑셀 날짜 관련 함수 총정리(2) (days, networkdays, datedif 함수)

2. 날짜끼리 더하고 빼기

날짜관련함수2.xlsx

0.02MB

① 연산자 +, - 를 이용해 더하고 빼기

날짜도 숫자와 동일한 성격이기 때문에 당연히 날짜끼리 더하고 뺄 수 있습니다. 빼는 것은 두 날짜사이의 기간을 구하는 것이고, 더하는 것은 둘 중 하나의 날짜만큼 미래의 날짜를 구하는 것이 됩니다.

​

㈀ D라는 글자 부분에서 마우스 오른쪽 버튼을 누른 후 삽입 메뉴를 눌러 D열을 삽입합니다.

 

㈁ F열도 삽입합니다.

F열 삽입시 위와 같이 F란 글자 부분을 눌러 삽입할 수도 있고, F열의 셀 하나를 선택한 다음 마우스 오른쪽 버튼을 누른 후 나오는 삽입 메뉴를 누른 후,

 

아래와 같은 선택 창에서 '열 전체'를 선택하고 '확인'버튼을 누르거나 '열 전체'를 더블 클릭해도 됩니다.

D열과 F열이 삽입 되었습니다.

 

㈂ D5셀에 날짜간 빼기, F5셀에 날짜간 더하기라고 입력하겠습니다.

 

㈃ 날짜끼리 빼기

ⓐ D6셀에

=c6-b6

라고 입력하고 엔터키를 칩니다.

​

ⓑ 구하는 것은 10인데, 1900-01-10이라고 표시됩니다. 날짜형식이므로 숫자형식으로 바꾸면 됩니다.

ⓒ 1900-01-10 왼쪽 오류표시기 아이콘을 클릭하면 될까 하고 눌러보니 적당한 것이 없습니다.

ⓓ 표시형식 선택 콤보박스의 오른쪽 역삼각형 모양을 클릭 후 일반 또는 숫자를 선택합니다.

ⓔ 숫자 서식을 선택하면 오른쪽에서 한 칸 떨어지는데, 일반 서식을 선택하면 떨어지는 것이 없습니다.옆의 날짜도 날짜 다음에 공백이 없으므로 일반을 선택하겠습니다.

(숫자 서식)

(일반 서식)

ⓕ 그런데 여전히 왼쪽 위에 오류표시 녹색 삼각형이 보입니다. 오류표시기 아이콘에서 '오류 무시'를 누르면

아래와 같이 녹색 표시가 없어졌지만 특별한 의미는 없습니다.

ⓖ 값이 10으로 C4셀의 10과 같습니다. 결국 B6셀에 10을 더한 날짜에서 B6셀을 뺐으니 당연히 10이 나오겠지요.

​

ⓗ D6셀 오른쪽 아래 채우기 핸들을 더블 클릭해서 D30셀까지 채웁니다. D6셀의 오류 표시를 제거하고 채우기를 했는데도 아래 셀들은 모두 오류 표시가 다시 나타났습니다. 신경쓰지 말고 그냥 넘어가도 됩니다.

ⓘ D6셀에

=b6-c6

이라고 입력하고 엔터키를 치면

-10이라고 나옵니다. 당연한 결과죠.

​

그런데 =-c6+b6이라고, 당초 수식 =c6-b6에서 +,- 부호만 바꿨더니 아래와 같이 표시됩니다. 이것은 표시형식이 '일반'에서 다시 '날짜'로 바뀌었기 때문으로

 

'일반'으로 바꾸면 -10이라고 정상적으로 표시됩니다.

​

㈄ 날짜끼리 더하기

ⓐ F6셀에

=b6+c6

라고 입력하고 엔터키를 칩니다.

​

ⓑ 그러면 2126-01-14라고 나옵니다.

ⓒ B6셀을 마우스로 클릭하고, Ctrl 키를 누른 상태에서 C6셀과 F6셀을 클릭해서 3개 셀을 선택한 다음, 표시형식을 '날짜'에서 '일반'으로 바꾸면 아래와 같이 숫자로 표시되는데, 41275+41285는 82560이 맞습니다. 날짜도 숫자라는 것을 쉽게 이해할 수 있죠?

 

ⓓ 위쪽 '한단계 취소(되돌리기)' 아이콘을 클릭하여 종전 상태로 돌립니다.

ⓔ F6셀의 채우기 핸들에 커서를 대고 더블클릭하거나, 채우기 핸들을 끌어서 F30셀까지 채웁니다. 더블클릭이 편합니다.

​

② days 함수

㈀ days 함수는 두 날짜 사이의 날짜 수를 구해주는 함수로, 연산자 - 를 사용한 것과 결과값이 동일하며, 입력형식은

=days(end_date, start_date) 로 종료일과 시작일의 두 인수가 필요합니다.

​

㈁ 먼저, G라고 쓰여 있는 부분을 클릭 후 H라고 쓰여 있는 부분까지 눌러 G열과 H열을 선택한 후, 마우스 오른쪽 버튼을 눌러 나오는 메뉴에서 '내용 지우기'메뉴를 클릭합니다.

기존에 입력된 내용이 지워집니다.

㈂ G5셀에 'days 함수' 라고 쓰고 엔터키를 칩니다.

㈃ G6 셀에

=days(c6,b6)

이라고 입력하고 엔터키를 칩니다.

​

㈄ 날짜 표시형식으로 나오는데, 일반으로 바꿉니다.

​

㈅ G6셀에

=days(b6,c6)

라고 입력하고 엔터키를 칩니다.

​

그러면 -10이란 값이 나옵니다. 그러니까 days함수는 종료일과 시작일의 위치가 중요합니다.

㈆ G6셀의 채우기 핸들에 커서를 대고 더블클릭하거나, 채우기 핸들을 끌어서 G30셀까지 채웁니다.

​

③ networkdays 함수

㈀ workday함수가 시작일을 기준으로 휴일과 주말을 제외한 근무일(평일) 수를 더하거나 뺀 날짜를 구하는 함수라면, networkdays 함수는 두 날짜 사이의 근무일(평일) 수를 구하는 함수입니다. 함수명을 보면 workday는 s가 없는 점이 다릅니다. net workdays 라고 외우면 편할 듯 합니다.

​

㈁ networkdays 함수의 입력형식은 =networkdays(start_date, end_date, [holidays])라고 되어 있어 시작일과 종료일은 필수 입력 인수이고, 휴일은 선택적 인수입니다. 순서가 workday 함수와 달리 시작일과 종료일이 반대입니다.

​

㈂ H5셀에 'networkdays함수'라고 입력하고 엔터키를 치고, Ctrl + 1 키를 누른 후 '맞춤'탭을 선택하고 '셀에 맞춤'에 체크하고 확인 버튼을 누릅니다.

 

㈃ 셀에 맞춰 글자가 작아집니다.

㈄ H6셀에

=networkdays(b6,c6,$i$6:$i$16)

라고 입력하는데,$i$6:$i$16는 직접 $표시를 입력할 필요없이, i6셀에서 i16까지 마우스로 끌어서 선택한 다음 F4키를 누르면 $표시가 앞뒤로 붙습니다. 그리고 나서, 오른쪽 괄호를 입력하고 엔터키를 칩니다.

㈅ 날짜 표시형식으로 보이므로

㈆ 홈 에뉴 아래 표시형식 콤보박스를 클릭해서 일반으로 변경합니다. 9로 표시됩니다.

​

㈇ 2013-01-01에서 2013-01-11 사이에 휴일과 주말을 제외한 근무일이 몇 일이 있나 아래 달력을 살펴보니, 2,3,4,7,8,9,10,11로 8일인데 9일로 나옵니다. 1월1일이 우리는 신정연휴인데 서양권은 휴일이 아니라 그렇습니다. networkdays 함수는 계산시 첫날을 포함하고, 말일도 포함하는데, workday함수는 첫날은 빼고 근무일수를 더하거나 빼는 점도 차이점입니다.

㈈ 그러면 휴일 데이터에 2013-01-01을 맨 아래 추가하고, 수식중 휴일범위를 $i$6:$i$17로 수정하고 엔터키를 칩니다.

㈉ 그러면 결과값이 8일로 바뀝니다.

㈊ H6셀 채우기 핸들을 더블 클릭해서 H30셀까지 채웁니다.

지금껏 사용했던 함수들의 결과값은 모두 같았는데, networkdays 함수의 결과값은 7일부터 9일까지 다릅니다.

​

H7셀의 값인 7을 검산하기 위해 살펴보니, 시작일이 2013-01-12이고, 종료일이 2013-01-22인데, 2013-01-12일과 13일은 주말이고, 19일과 20일도 주말이라 첫째날을 포함하니 총 11일에서 4일을 빼니 7일이 됩니다.

​

이번에는 결과값인 H13셀을 검산해 보면, 시작일이 2013-03-19이고, 종료일이므로 아래 달력을 기준으로 근무일 수를 계산해 보면 19,20,21,22,25,26,27,28,29로 9일이 맞습니다.

​

networkdays함수도 days 함수처럼 시작일과 종료일 순서를 바꿔 입력하면 마이너스 값이 나옵니다.

​

④ datedif 함수

㈀ datedif함수는 함수마법사 목록에 없는 함수로서, 입력형식이 =datedif(시작일, 종료일, 단위) 로서 세 개의 인수가 필요하면, 단위를 기준으로 시작일과 종료일의 차이를 계산해주는 함수이며, 단위는

y(연), m(월), d(일), ym(연으로 올리고 남은 월수), md(월로 올리고 남은 일수), yd(연을 제외한 월과 일의 차이로서, 일수로 환산한 수) 가 있습니다.

​

date difference를 생각하면 함수명을 외우기 쉬울 듯 합니다. 그러나 엑셀 VBA에 유사한 datediff 란 함수가 있어 헷갈리는 것은 마찬가지입니다.

​

㈁ days와 networkdays와는 아래와 같은 차이점이 있습니다.

ⓐ days와 networkdays 는 시작일과 종료일을 바꾸면 마이너스 값이 나오는데, datedif 함수는 #NUM! 에러가 발생합니다.

ⓑ datedif 함수도 휴일을 고려하지 않으므로 days 함수와 유사한데, days는 날짜 차이만을 계산해주는데, 이것을 연, 월, 일 등 차이를 계산해줍니다.

​

㈂ 맨 처음에 첨부한 엑셀 파일에서 sheet2 를 클릭합니다. 경우의 수에 따라 시작일과 종료일을 만들어 놓았습니다.

㈃ F4셀에 수식을

=datedif($d4,$e4,f$3)

라고 입력하는데, $d4는 마우스로 d4셀을 클릭하고 F4키를 세번 누르고, $e4는 마우스로 e4셀을 클릭하고 F4키를 세번 누르고, f$3는 마우스로 f3셀을 클릭하고 F4키를 두번 눌러 입력하면 편합니다. 제일 오른쪽 괄호는 입력하지 않고 엔터키를 쳐도 자동으로 입력되니 참고하시기 바랍니다.

​

$d4라고 입력하는 이유는 셀을 오른쪽 또는 아래로 복사하더라도 D열이 바뀌면 안되기 때문이며,

$e4라고 입력하는 이유도 위와 같이 셀을 오른쪽 또는 아래로 복사하더라도 E열이 바뀌면 안되기 때문입니다.

마찬가지로 f$3는 셀을 오른쪽 또는 아래로 복사하더라도 단위를 3행으로 고정하기 위해서 입니다.

㈄ 연월일이 같기 때문에 햇수 차이가 없어 값이 0입니다.

​

㈅ F4셀의 오른쪽 아래 네모 모양의 채우기 핸들을 오른쪽으로 끌어서 K4셀까지 수식을 복사하고, 다시 K4셀의 채우기 핸들을 클릭후 K21셀까지 복사하면 될 듯 하지만 테두리가 영향을 받아 아래와 같이 세로가 굵은 줄이 되므로 다른 방법을 써야 합니다.

㈆ Ctrl + Z키를 두번 눌러 취소하거나, 윗쪽 실행취소 아이콘을 두번 눌러 원상태로 돌리거나,

실행 취소 아이콘 오른쪽의 콤보박스를 누른 후 나오는 목록에서 세번째 줄을 클릭해도 됩니다. 위 두 줄은 자동 채우기를 두 번 했다는 것입니다.

㈇ F4셀에서 마우스 오른쪽 버튼을 눌러 복사를 클릭한 다음,

㈈ F4셀에서 K21셀까지 마우스로 끌어서 선택하고, fx라고 표시된 '수식으로 붙여넣기' 아이콘을 클릭합니다.

㈉ 아래와 같이 테두리에 영향을 주지 않고 붙여넣기가 깔끔하게 됐습니다.

㈊ K21셀의 수식을 보면 =DATEDIF($D21,$E21,K$3) 로 위치에 맞게 제대로 붙여넣어졌음을 알 수 있습니다.

㈋ 결과값을 살펴보면 y,m,d는 연,월,일수 차이를 보여주는 것이니 간단한데, 다만 F10셀에서 K12셀까지 #NUM! 에러가 난 것은 종료일이 시작일보다 작기 때문입니다.

​

ym은 연으로 올리고 남은 월수만을 계산해 주는데, i7셀에서 i9셀이 g7셀에서 g9셀과 결과값이 같습니다. 이는 연(햇수) 차이가 0이기 때문에 똑같은 것이고, 아래 g13셀에서 g21셀과 i13셀에서 i21셀을 보면, g열은 모두 21보다 큰데, i열은 ,1년이 12개월이므로, 이를 뺀 월 수만 남아 있습입니다. 24는 2년으로 모두 연으로 가져 갔으므로, 월이 남아 있는게 없죠.

​

md도 마찬가지로 월로 올리고 남은 일수만 계산해주므로 모두 31일보다 클 수가 없습니다.

​

yd는 연도는 고려하지 않고, 월일의 차이를 일수로 환산한 것으로 364(윤년은 365)보다 클 수가 없습니다.

​

㈌ 차이를 확실하게 알 수 있도록 L열에서 N열에 수식을 추가하겠습니다.

L4셀에

=g4-i4

라고 입력하고,

​

M4셀에

=h4-j4

라고 입력하고,

​

N4셀에

=h4-k4

라고 입력한 다음

​

마우스로 L4셀에서 N4셀까지 끌어서 선택하고,

N4셀 오른쪽 아래 채우기 핸들을 N21셀까지 끌면 될 듯 하지만,

이 또한 맨 아래줄이 가는 줄로 바뀌는 문제점이 있으므로 안됩니다.

㈍ 따라서 Ctrl + Z키를 한번 눌러 원 상태로 돌리고, L4셀에서 N4셀까지 마우스로 끌어서 선택한 후(이미 선택된 상태라면 다시 선택하지 않아도 됩니다) 마우스 오른쪽 버튼을 눌러 복사를 클릭하고,

L5셀에서 N21셀까지 마우스로 끌어서 선택하고, 마우스 오른쪽 버튼을 누른 후 '수식으로 붙여넣기' 아이콘을 클릭해야 합니다.

'수식으로 붙여넣기'아이콘에 마우스를 갖다 대면 위와 같이 붙여넣기된 모습이 보입니다. 아이콘을 클릭하여 붙여넣기를 완료합니다.

​

㈎ 아래 표에서 차이를 살펴보면

m과 ym은 햇수(y)에 해당하는 월수만큼 차이나는 것을 알 수 있고,

d와 md는 월에 해당하는 날수만큼 차이나는 것인데, 계산해봐야 확인할 수 있겠지만 맞는 듯 합니다.

d와 yd는 y에 해당하는 일수만큼 차이가 나는 것을 확인할 수 있습니다.

㈏ e21셀을 윤년인 2016-06-10으로 바꾸면 N21셀의 값이 1096이 되는데, 365*3=1095임과 비교할 때 1이 크므로 윤년이 반영된 것을 알 수 있습니다.

날짜관련함수2(완성).xlsx

0.02MB