2016.09.12

“알면 아주 편한” 엑셀의 날짜 및 시간 관련 개념과 활용법 정복

JD Sartain | PCWorld
엑셀에서 날짜를 계산할 땐 일련번호(Serial Numbers)라는 고유의 시스템을 사용할 수 있다. 모든 날짜(월, 일, 년도)와 시간(시간, 분, 초)는 1900년 1월 1일 00시 00분 00초부터 시작되는 일련번호와 대응된다. 많은 프로그래밍 언어에서 사용되는 율리우스 날짜(Julian dates, 매년 1월 1일 0시 이후 경과된 날을 표기하는 날짜)와 혼돈해선 안 된다. 엑셀 일련번호는 마이크로소프트 제품에서만 통하는 고유의 숫자다.

먼저, 엑셀을 열고 A3부터 A7까지 1~5로 채운다. 이 영역을 선택한 다음 홈 탭의 ‘표시 형식’의 숫자 표시 형식 드롭다운 메뉴에서 ‘간단한 날짜’를 선택한다. 1부터 5까지의 숫자가 1900-01-01부터 1900-01-05까지로 변경되는 것을 확인할 수 있다.





이제 B3에서 B7까지 2016-05-01부터 2016-05-05까지 입력한다. 이 영역을 선택한 다음 홈 탭의 숫자 표시 형식 드롭다운 메뉴에서 ‘일반’을 선택한다. 이 날짜들이 42491부터 42495로 변경되는 것을 확인할 수 있다.

이 숫자들은 2016년 5월의 첫 5일을 나타내는 숫자로, 엑셀은 이것을 일련번호’라고 부르며 날짜와 시간을 계산할 때 사용한다.

이제 C3에 엑셀의 일련번호를 율리우스 날짜로 변경해보도록 하겠다.

=RIGHT(YEAR(B1),2)& B1-DATE(YEAR(B1),1,0). 이것은 스프레드시트 날짜에서 데이터베이스나 타임스탬프 애플리케이션 등 다른 프로그램으로 날짜를 추출할 때 사용할 수 있는 함수다.

율리우스 날짜를 일련번호로 변경하는 수식은 다음과 같다.
=DATE(1900+INT(Julian_Date/1000),1,MOD(Julian_Date,1000))


두 날짜 사이의 일수 계산하기
하루에 10만원짜리 아르바이트를 하기로 했다고 가정해보자. 2016년 2월 21일부터 2016년 5월 1일까지 매일 일한다고 생각하면, 얼마를 벌게 될까? 수작업으로 계산하는 것보다 엑셀로 훨씬 빠르게 계산할 수 있다.

이 계산을 위한 수식은 간단하다. 시작 날짜에서 종료 날짜를 빼기만 하면 된다.

=SUM(B1-A1). 답은 70일이다. 이제 총 수익을 계산하려면 일당을 곱하기만 하면 된다.

=SUM(B1-A1)*100000. 총 700만 원을 번다는 계산이 나온다.


 


이제 같은 기간 동안 일을 하되 평일에만 일하면 얼마나 벌 수 있는지 계산해보자. A3과 B3에 시작과 종료 날짜를 입력하고 C3에 =NETWORKDAYS(A3,B3)를 입력한다. 50일이라는 새로운 답이 얻어진다. 혹은 수식 탭에서 ‘날짜 및 시간’에서 NETWORKDAYS를 선택한다. 함수 인수 상자에 시작과 끝 날짜가 입력된 셀 번호를 입력한 다음 ‘확인’을 클릭한다. 똑같이 50일이라는 결과가 나올 것이다. 이제 해당 셀에 일당을 곱하면 된다.

=NETWORKDAYS(A3,B3)*100000. 평일만 일했을 경우 버는 돈은 50만 원이다.

텍스트 날짜를 실제 날짜로 바꾸기
엑셀에 날짜를 복사해서 넣으면 텍스트로 입력된다. 이 경우 DATEVALUE 함수를 사용해서 텍스트 날짜를 엑셀의 일련번호로 전환할 수 있다. 이를 테스트해보려면 먼저 A5부터 A9까지 텍스트로 5개의 날짜를 입력한다.

커서를 B5로 옮기고, 수식 탭에서 ‘날짜 및 시간’에서 DATEVALUE를 선택하고, 날짜 텍스트가 입력되어 있는 셀 A5를 클릭한다. B5에는 일련번호가 입력되며, 해당 수식을 아래로 복사하면 각 텍스트 날짜에 해당되는 일련번호가 입력된다.






이제 원하는 날짜 표시 형식으로 변환할 차례다. B5부터 B9까지 선택한 다음 홈 탭의 표시 형식 드롭다운 메뉴에서 원하는 방식을 선택한다. 만일 조금 더 고급 옵션이 필요하다면, 표시형식 섹션 오른쪽 아래의 화살표를 클릭하면 셀 서식 창이 나타난다. 범주에서 ‘날짜’를 선택하고, 오른쪽의 ‘형식’에서 원하는 것을 선택한다. 이제 엑셀의 일련번호가 실제 날짜로 표시되며, 원하는 함수에도 적용할 수 있다.


모든 날짜 형식을 위한 일련번호
모든 사업체는 데이터베이스와 스프레드시트를 사용해서 월급, 지출관리, 영업 보고서, 회계 등을 처리한다.

엑셀은 월부터 초까지 각 단위의 날짜와 시간에 대한 일련번호를 제공한다. A1셀에 커서를 두고 수식 탭에서 ‘날짜 및 시간’을 클릭하고 NOW를 선택한다. 확인을 클릭하면 팝업창이 나타난다.



홈 탭에서 셀 섹션에서 ‘서식’을 클릭하고 드롭다운 메뉴에서 ‘셀 서식’을 선택한다. 원하는 형식을 선택한 다음 ‘확인’을 클릭한다.

이제 이 날짜를 엑셀의 일련번호로 변환한다. 앞서 이야기한 것처럼 홈 탭의 표시형식에서 ‘일반’을 선택하면 된다. 이제는 일련번호를 평일, 월, 년도, 시간, 분, 초로 표시하는 방법을 알아보겠다.


서식 > 함수 라이브러리 > 날짜 및 시간에서 ‘DAY’를 선택한 다음 팝업 창에서 A1 셀을 넣는다. 그러면 오늘의 ‘일’을 표시해준다. 같은 방법을 월, 년도, 시간, 분, 초에 적용할 수 있다.

=MONTH(A1) : 오늘의 월
=YEAR(A1) : 오늘의 년도
=HOUR(A1) : 지금의 시
=MINUTE(A1) : 지금의 분
=SECOND(A1) : 지금의 초

앞서 살펴본 모든 함수를 통해서 크리스마스 전까지 평일이 얼마나 남았는지, 윤년 2월에 몇 시간이나 추가되는 지 등을 확인할 수 있다. editor@itworld.co.kr


2016.09.12

“알면 아주 편한” 엑셀의 날짜 및 시간 관련 개념과 활용법 정복

JD Sartain | PCWorld
엑셀에서 날짜를 계산할 땐 일련번호(Serial Numbers)라는 고유의 시스템을 사용할 수 있다. 모든 날짜(월, 일, 년도)와 시간(시간, 분, 초)는 1900년 1월 1일 00시 00분 00초부터 시작되는 일련번호와 대응된다. 많은 프로그래밍 언어에서 사용되는 율리우스 날짜(Julian dates, 매년 1월 1일 0시 이후 경과된 날을 표기하는 날짜)와 혼돈해선 안 된다. 엑셀 일련번호는 마이크로소프트 제품에서만 통하는 고유의 숫자다.

먼저, 엑셀을 열고 A3부터 A7까지 1~5로 채운다. 이 영역을 선택한 다음 홈 탭의 ‘표시 형식’의 숫자 표시 형식 드롭다운 메뉴에서 ‘간단한 날짜’를 선택한다. 1부터 5까지의 숫자가 1900-01-01부터 1900-01-05까지로 변경되는 것을 확인할 수 있다.





이제 B3에서 B7까지 2016-05-01부터 2016-05-05까지 입력한다. 이 영역을 선택한 다음 홈 탭의 숫자 표시 형식 드롭다운 메뉴에서 ‘일반’을 선택한다. 이 날짜들이 42491부터 42495로 변경되는 것을 확인할 수 있다.

이 숫자들은 2016년 5월의 첫 5일을 나타내는 숫자로, 엑셀은 이것을 일련번호’라고 부르며 날짜와 시간을 계산할 때 사용한다.

이제 C3에 엑셀의 일련번호를 율리우스 날짜로 변경해보도록 하겠다.

=RIGHT(YEAR(B1),2)& B1-DATE(YEAR(B1),1,0). 이것은 스프레드시트 날짜에서 데이터베이스나 타임스탬프 애플리케이션 등 다른 프로그램으로 날짜를 추출할 때 사용할 수 있는 함수다.

율리우스 날짜를 일련번호로 변경하는 수식은 다음과 같다.
=DATE(1900+INT(Julian_Date/1000),1,MOD(Julian_Date,1000))


두 날짜 사이의 일수 계산하기
하루에 10만원짜리 아르바이트를 하기로 했다고 가정해보자. 2016년 2월 21일부터 2016년 5월 1일까지 매일 일한다고 생각하면, 얼마를 벌게 될까? 수작업으로 계산하는 것보다 엑셀로 훨씬 빠르게 계산할 수 있다.

이 계산을 위한 수식은 간단하다. 시작 날짜에서 종료 날짜를 빼기만 하면 된다.

=SUM(B1-A1). 답은 70일이다. 이제 총 수익을 계산하려면 일당을 곱하기만 하면 된다.

=SUM(B1-A1)*100000. 총 700만 원을 번다는 계산이 나온다.


 


이제 같은 기간 동안 일을 하되 평일에만 일하면 얼마나 벌 수 있는지 계산해보자. A3과 B3에 시작과 종료 날짜를 입력하고 C3에 =NETWORKDAYS(A3,B3)를 입력한다. 50일이라는 새로운 답이 얻어진다. 혹은 수식 탭에서 ‘날짜 및 시간’에서 NETWORKDAYS를 선택한다. 함수 인수 상자에 시작과 끝 날짜가 입력된 셀 번호를 입력한 다음 ‘확인’을 클릭한다. 똑같이 50일이라는 결과가 나올 것이다. 이제 해당 셀에 일당을 곱하면 된다.

=NETWORKDAYS(A3,B3)*100000. 평일만 일했을 경우 버는 돈은 50만 원이다.

텍스트 날짜를 실제 날짜로 바꾸기
엑셀에 날짜를 복사해서 넣으면 텍스트로 입력된다. 이 경우 DATEVALUE 함수를 사용해서 텍스트 날짜를 엑셀의 일련번호로 전환할 수 있다. 이를 테스트해보려면 먼저 A5부터 A9까지 텍스트로 5개의 날짜를 입력한다.

커서를 B5로 옮기고, 수식 탭에서 ‘날짜 및 시간’에서 DATEVALUE를 선택하고, 날짜 텍스트가 입력되어 있는 셀 A5를 클릭한다. B5에는 일련번호가 입력되며, 해당 수식을 아래로 복사하면 각 텍스트 날짜에 해당되는 일련번호가 입력된다.






이제 원하는 날짜 표시 형식으로 변환할 차례다. B5부터 B9까지 선택한 다음 홈 탭의 표시 형식 드롭다운 메뉴에서 원하는 방식을 선택한다. 만일 조금 더 고급 옵션이 필요하다면, 표시형식 섹션 오른쪽 아래의 화살표를 클릭하면 셀 서식 창이 나타난다. 범주에서 ‘날짜’를 선택하고, 오른쪽의 ‘형식’에서 원하는 것을 선택한다. 이제 엑셀의 일련번호가 실제 날짜로 표시되며, 원하는 함수에도 적용할 수 있다.


모든 날짜 형식을 위한 일련번호
모든 사업체는 데이터베이스와 스프레드시트를 사용해서 월급, 지출관리, 영업 보고서, 회계 등을 처리한다.

엑셀은 월부터 초까지 각 단위의 날짜와 시간에 대한 일련번호를 제공한다. A1셀에 커서를 두고 수식 탭에서 ‘날짜 및 시간’을 클릭하고 NOW를 선택한다. 확인을 클릭하면 팝업창이 나타난다.



홈 탭에서 셀 섹션에서 ‘서식’을 클릭하고 드롭다운 메뉴에서 ‘셀 서식’을 선택한다. 원하는 형식을 선택한 다음 ‘확인’을 클릭한다.

이제 이 날짜를 엑셀의 일련번호로 변환한다. 앞서 이야기한 것처럼 홈 탭의 표시형식에서 ‘일반’을 선택하면 된다. 이제는 일련번호를 평일, 월, 년도, 시간, 분, 초로 표시하는 방법을 알아보겠다.


서식 > 함수 라이브러리 > 날짜 및 시간에서 ‘DAY’를 선택한 다음 팝업 창에서 A1 셀을 넣는다. 그러면 오늘의 ‘일’을 표시해준다. 같은 방법을 월, 년도, 시간, 분, 초에 적용할 수 있다.

=MONTH(A1) : 오늘의 월
=YEAR(A1) : 오늘의 년도
=HOUR(A1) : 지금의 시
=MINUTE(A1) : 지금의 분
=SECOND(A1) : 지금의 초

앞서 살펴본 모든 함수를 통해서 크리스마스 전까지 평일이 얼마나 남았는지, 윤년 2월에 몇 시간이나 추가되는 지 등을 확인할 수 있다. editor@itworld.co.kr


X