날짜와 시간 계산하는 법

[양성일]

시간 더하기

=SUM함수를 이용하면 시간을 더할 수 있다. 모든 시간을 HH:MM:SS로 입력하고 이들을 더하면 된다. SS부분은 입력하지 않아도 된다. 기본적으로 엑셀은 시의 합계를 하루의 시간형태로 표시한다. 즉, 12:30 + 12:45 은 01:15라고 표시된다. 24시간이 넘는 시간을 표시하려면 셀서식을 "[h]:mm"로 설정해 주어야 한다. 그러면 01:15이 아닌 25:15로 표시된다.

분과 초를 더하고자 한다면 앞자리를 "0:"을 사용해야한다. 예를 들면 10분 20초를 입력하려면 0:10:20을 입력해야한다. 이들을 더하기하면 엑셀은은 그 결과를 하루의 시간 형태로 보여준다. 즉, 0:40:10에 0:30:20를 더하면 1:10:30라고 표시된다. 60분이 넘는 분을 표시하려면 셀 서식을 "[m]:ss"와 같이 설정해 주면 1:10:30 대신에 70:30으로 표시해준다.

또 다른 방법은 =TIME함수를 사용하는 것이다. 1시간 35분 10초를 A1에 더하려면 =A1 + TIME(1,35,10)을 사용하면 된다.

---

시간 빼기

엑셀이 음의 시간을 취급하지 않기 때믄에 어떤 시간에서 다른 시간을 빼는 것은 약간 어렵다. 날짜 없이 시간을 입력하면 엑셀은 이를 1900년 1월 1일의 시간으로 인식하기 때문에 일련번호의 날짜 부분에 0을 넣어 버린다. 예를 들어 오후 4:00에서 18시간을 뺄 수가 없다. 이유는 이 계산결과가 음의 수가 되기 때문이다. (0.67 - 0.75 = -0.83).

그래서 방법은 시간을 입력할 때 모든 말짜를 포함하여 시간을 입력하는 것이다. 그리고 결과 값을 시간만 나타나도록 서식을 지정해 주면 된다. 예를 들어 오후 4:00에서 18:00을 빼고자하면 4:00PM대신에 98/1/1 4:00 PM이라고 넣고 18:00을 여기서 뺀다. 그리고 결과셀의 서식을 "hh:mm "라 지정하면 "10:00 PM" 라고 표시된다.

또 다른 방법은 , =TIME 함수를 사용하는 것이다. A1의 날짜 시간에서 10시간 15분을 빼려면 =A1-TIME(10,15,0)을 사용하면 된다.

 맨위로

---

 

시간 차이계산 (Time Intervals)

두 시간간의 차이를 구하려면 이들 두 시간을 빼주면 된다. 그러나 엑셀은 음의 시간을 취급할 수 없으므로 IF구문을 사용해야 한다. 시간에 날자를 입력하지 않았다면, 아래의 식으로 두개의 시간 A1,B1의 차이를 구할 수 있다.

=IF(A1>B1,B1+1-A1,B1-A1)

여기서 +1은 B1을 다음날로 취급하기 위한 것이다. 그렇게 함으로써 02:00-22:00의 결과가 4:30 즉, 4시간 30분이 되는 것이다. 즉,

=24*(IF(A1>B1,B1+1-A1,B1-A1))

이 것을 4.5와 같이 십진수로 몇시간인가를 나타내려면 결과값에 24를 곱해주면 된다. 그리고 셀의 서식을 일반, 또는 십진수로 설정하년 된다.

  맨위로

---

 

날짜 더하기/빼기(Adding Dates)

날짜에 날짜수를 더하거나 빼는 것은 그냥 더하거나 빼기만하면 된다. 예) A1(1998/1/1)에 5일을 더하려면

=A1 + 5 라고,  빼려면 =A1 - 5 를 하면 된다.

날짜에 개월 수나 햇수를 더하거나 빼려면, 우선 날짜의 성분을 분리해서 각각을 더하거나 빼고 이를 다시 조합해야 한다.

아래의식은 A1에 있는 날짜에 3개월 4일을 더하는 식을 나타낸 예이다.

=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)+4)

엑셀은 자동적으로 MONTH 함수가 12를 초과하거나, DAY함수가 31 을 초과하면 위단위를 올려준다.

예를 들면, 97-8-25에 6개월 10일을 더하면 98-8-25을 되돌려준다.(빼기의 경우에는 +를 -로 바꾸어주면 된다.)

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

일련번호로된 두 날짜는 더하거나 뺄 수 없다. 예를 들면, 1998/1/15 과 1998/6/15을 더하면 의미가 없는 2096/6/30을 되돌린다.

  맨위로

---

 

나이 계산법

생일로 부터 나이를 구하는 것은 비교적 쉽다. A1에 생일이 있다고 할때 아래 식은 나이를 몇 년 몇 개월 몇 일로 나타내준다.

=DATEDIF(A1,NOW(),"y")&" 년 "& DATEDIF(A1,NOW(),"ym")&" 월 " & DATEDIF(A1,NOW(),"md") & "일"

  맨위로

---

 

한 달의 날짜수

한 달에 몇일이 있을까를 알아 내려면 다음의 식을 사용하면 된다. 여기서 A1에 1998/3/15이 입력되어 있다 가정한다.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

이 식은 31을 되돌린다. 즉, 1998년 1월에는 31일이 있다는 뜻이다.

 맨위로

---

 

월의 마지막 날짜

아래의 식은 한달의 마지마가 날짜를 되돌린다. AA1에 1998/1/15이 있다고 가정

=DATE(YEAR(A1),MONTH(A1)+1,0)

이 식은 98-01-31을 되돌린다.

 맨위로

---

 

지난 달의 마지막 날짜

아래 식은 지난 달의 마지막 날짜를 되돌린다. AA1에 1998/1/15이 있다고 가정

=DATE(YEAR(A1),MONTH(A1),0)

이 식은 97-12-31을 되돌린다.

 맨위로

---

 

특정기간 중 월요일이 몇번 있을까?

두 날짜 사이에 나타나는 월요일이 몇번 일까를 알고자 할 때에는 다음의 배열수식을 사용하면 된다.이 식은 배열식이므로 식을 입력한 후 Ctrl + Shift + Enter를 눌러야 한다.

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

위의 식은 다음과 같다고 가정하였다.

A2 : 시작하는 날짜

B2 : 끝나는 날짜

C2 : 구하려는 요일 번호(1=Sunday, 2=Monday, ... , 7=Saturday)

 맨위로

---

 

두 날짜간에 몇주일이 있을까?

두 날짜간에 있는 주일 수는 =DATEDIF를 사용하여 구할 수가 없다. 따라서 아래와 같이 수식을 만들어 사용해야 한다. 두 날짜 사이의 날 수를 7로 나누어 정수부만 취해 주(週) 수를 구하고, 날짜 수를 7로 나눈 나머지를 더하여 구한다.

=TRUNC((B1-A1)/7)&" 주일 "&MOD(B1-A1,7)&" 일"

 맨위로

---

 

무슨 요일일까?

어느 특정일이 무슨요일인가를 알아내려고 =WEEKDAY 함수를 사용하면 1 ~ 7까지 요일을 나타내는 해당 숫자를 되돌린다.(1=Sunday, 2=Monday, ... , 7=Saturday) 이 것을 해당하는 요일로 표시하려면 =TEXT함수를 사용한다.

=TEXT(A1, "ddd") 은 세자로된 영문자 요일을 표시한다. 예. "Mon".

=TEXT(A1,"dddd") 은 완전한 영문자 요일을 표시한다. 예 "Monday".

 맨위로

---

 

몇 번째 주일일까?

어느 날짜가 일년 중 몇 번째 주에 속하는 것일까를 알아내려면, 다음식을 사용한다.

=TRUNC(((A1-DATE(YEAR(A1),1,1)) / 7))+1+ IF(WEEKDAY(DATE(YEAR(A1),1,1)) > WEEKDAY(A1),1,0)

이 식은 1 ~ 53 사이의 값으로 나타난다.

 맨위로 

---

두 날짜 사이의 날짜 계산(=DATEDIF 함수)

=DATEDIF 함수는 워크시트 함수로 두 날짜사이의 기간을 계산하는 함수다. 이상하게도 이 함수는 엑셀의 내장함수 임에도 불구하고 함수 리스트 및 도움말에 나오지 않는다.

=DATEDIF 의 구문 구성은 다음과 같다.

=DATEDIF (Date1, Date2, Interval)

여기서

Date1 : 첫 번째 날짜로 엑셀의 표준 일련번호 형식이다.

Date2 : 두 번째 날짜로 엑셀의 표준 일련번호 형식이다.

Interval : 함수의 결과 값으로 반환할 시간단위

Date1은 Date2보다 앞 선 날짜이어야 한다. 그렇지 않으면 이 함수는 #NUM!에러를 낸다. Date1이나 Date2가 날짜가 아니면 #VALUE! 에러가 난다.

Interval은 아래의 항목중 하나여야 한다.

< Interval Code 설명 >

코드	의  미	해   설
"m"	Month	두 날짜 사이의 개월 수
"d"	Days	두 날짜 사이의 일 수 (날짜수)
"y"	Years	두 날짜 사이의 연 수
"ym"	Months Excluding Year	두 날짜 사이의 월 수로, 두 날짜가 같은 해에 있다고 가정
"yd"	Days Excluding Year	두 날짜 사이의 날짜 수로, 두 날짜가 같은 해에 있다고 가정
"md"	Days Excluding Months And Years	두 날짜 사이의 날짜 수로, 두 날짜가 같은 해, 같은 월에 있다고 가정

=DATEDIF 함수에 Interval인수를 문자열로 입력을 하려면 아래의 예와 같이 따옴표를 사용해야 한다.

예) =DATEDIF(A1, NOW(), "d")

그러나 Interval 인수가 워크시트의 셀에 들어 있다면 해당 셀 주소를 입력하면 된다