1. 아별툴
  2. 아별툴 패밀리
  3. 엑셀 질문하기
  4. 엑셀 강좌
  5. 엑셀 팁
  6. 엑셀 자료실
  7. 엑셀 연구과제
  8. 엑셀 북마크
  9. 관련 프로그램 소개


출처 : 아별닷컴(http://www.abyul.com/zbxe/20629)
작성일시 : 2008.06.30 15:51:24
저작자 : 아별닷컴 주인장 오주원(abyul@paran.com)

엑셀 다운 받기 : AbyulDotCom_Exel_convertDateToOtherFormat_20090203.xls


 

* 참고로 MS 고객지원 사이트에도 두 날짜 사이에 계산에 대한 수식이 나와 있네요.
* 주차를 알려주는 WEEKNUM() 함수를 사용하려면 도구 > 추가기능 ==> 분석도구 를 추가해야한다.


아래 1979-01-07 이라는 값이 들어 있는 셀은 A2셀이다.


▼ 추가 (2009.03.05) ------------------------------------------------------------

시스템에서 다운 받은 데이터가 월/일만 있을때(특히 더존PLUS.. =_=;;).. 날짜 데이터로 가공하는 방법.
아래쪽의 SMART한(?) 방법으로 처리하자. 예전에는 무식한 방법으로.. =_=;; 지금 생각하면 부끄.. ㅋ

DATE1

DATE2

 

 

1979-01-07

2009-02-03

* A2셀에 있는 날짜 데이터에 대한 포멧 변환

 구분 

결과값

 수식 

 비고 

 년으로 변환  1979  =IF(A2="","",TEXT(A2,"YYYY"))   
 반기로 변환  상반기  =IF(A2="","",IF(MONTH(A2)<=6,"상반기","하반기"))   
 분기로 변환  1Q  =IF(A2="","",IF(MONTH(A2)<=3,"1Q",IF(MONTH(A2)<=6,"2Q",IF(MONTH(A2)<=9,"3Q","4Q"))))   
 월로 변환  01  =IF(A2="","",TEXT(A2,"MM"))   
 일로 변환  07  =IF(A2="","",TEXT(A2,"DD"))   
 년기준 주차  2  =WEEKNUM(A2,1)   
 월기준 주차-1  2  =IF(MONTH(A2)=1,WEEKNUM(A2,1),IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2),0),1)=7,WEEKNUM(A2,1)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),0),1),WEEKNUM(A2,1)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),0),1)+1))   
 월기준 주차-2  2  =IF(MONTH(A2)=1,WEEKNUM(A2,1),IF(WEEKDAY(EOMONTH(A2,-1),1)=7,WEEKNUM(A2,1)-WEEKNUM(EOMONTH(A2,-1),1),WEEKNUM(A2,1)-WEEKNUM(EOMONTH(A2,-1),1)+1))   
 해당월 말일-1  1979-01-31  =DATE(YEAR(A2),MONTH(A2)+1,0)   
 해당월 말일-2  1979-01-31  =EOMONTH(A2,0)   
 전월 말일-1  1978-12-31  =DATE(YEAR(A2),MONTH(A2),0)   
 전월 말일-2  1978-12-31  =EOMONTH(A2,-1)   
 해당월 날짜수                           31  =DAY(DATE(YEAR(A2),MONTH(A2)+1,0))   
* 날짜 계산(더하기/빼기)

 구분 

결과값

 수식 

 비고 

 10일 뒤  1979-01-17  =A2+10   
 3달 뒤  1979-04-07  =DATE(YEAR(A2),MONTH(A2)+3,DAY(A2))   
 5년 6개월 일주일 후  1984-07-14  =DATE(YEAR(A2)+5,MONTH(A2)+6,DAY(A2)+7)   
 * 두 날짜 사이에 차이 계산 

 구분 

결과값

 수식 

 비고 

 년수                           30  =YEAR(B2)-YEAR(A2)   
 년/개월/일자 수   30년 0개월 27일   =DATEDIF(A2,NOW(),"Y")&"년 "& DATEDIF(A2,NOW(),"YM")&"개월 " & DATEDIF(A2,NOW(),"MD") & "일"   
 개월수-1                         360  =DATEDIF(A2,B2,"M")   일자 단위는 무시하려면 아래 수식 참조 
 개월수-2                         361  =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)   같은달이면 같은 결과.. 날짜 단위는 무시된다. 
 일자-1                     10,985  =B2-A2   
 일자-2                     10,985  =DATEDIF(A2,B2,"D")   
 주차   1,569주 2일   =TEXT(TRUNC((B2-A2)/7),"#,###")&"주 "&MOD(B2-A2,7)&"일"   
* 부록 - 텍스트로 저장된 날짜 데이터를 날짜 형식 데이터로 변환하기

 구분 

결과값

 수식 

 비고 

  4/ 6  2009-04-06  =IF(ISERROR(FIND("/",TRIM(A44))),"",DATE(YEAR(NOW()),VALUE(LEFT(TRIM(A44),FIND("/",TRIM(A44))-1)),VALUE(RIGHT(TRIM(A44),LEN(TRIM(A44))-FIND("/",TRIM(A44))))))   이것보단 바로 아래 VALUE()함수 사용 방법을 추천 
  4/29  2009-04-29  =IF(ISERROR(FIND("/",TRIM(A45))),"",DATE(YEAR(NOW()),VALUE(LEFT(TRIM(A45),FIND("/",TRIM(A45))-1)),VALUE(RIGHT(TRIM(A45),LEN(TRIM(A45))-FIND("/",TRIM(A45)))))) 
  4/ 6  2009-04-06  =VALUE(A46)   이게 최고! +_+)b 
  20060901   2006-09-01  =DATE( LEFT(TRIM(A47),4), MID(TRIM(A47),5,2), RIGHT(TRIM(A47),2) )   요건 어쩔 수 없이 이걸 사용 
  2008.04.21   2008-04-21  =DATE( LEFT(TRIM(A48),4), MID(TRIM(A48),6,2), RIGHT(TRIM(A48),2) )   이것보단 바로 아래 SUBSTITUTE()함수 사용 방법을 추천 
  2008.04.21   2008-04-21  =VALUE(SUBSTITUTE(A49,".","/"))   데이터 사이에 /나 - 들어가 있으면 VALUE()함수로 다 커버된다. 
  2006-04-21   2006-04-21  =VALUE(A50) 
  2006-4-6   2006-04-06  =VALUE(A51) 
  2008/4/06  2008-04-06  =VALUE(A52) 
  2008/04/6  2008-04-06  =VALUE(A53) 
  08/ 4/ 6  2008-04-06  =VALUE(A54) 


▼ 추가 (2009.03.03) ------------------------------------------------------------

월기준 누적분 (T1셀에는 기준 월을 적는다. 6 또는 9 등)
=IF(MONTH(B2)<=$T$1,$T$1&"월누적",$T$1&"월초과")

직전 6개월 계산 (AI1셀에는 =NOW() 또는 기준일을 입력해 놓는다.)
=IF(DATEDIF($A2,AI$1,"M")<=6,"직전6개월","X")

월별 변동 폭 확인시 (월별 변동폭을 표현할때, 표의 머릿말용이다.)
=TEXT(VALUE(N20),"m월")&TEXT(VALUE(O20),"→m월")
=TEXT(VALUE(M121),"m월")&TEXT(VALUE(N121),"→m월")

▲ 추가 끝 (2009.03.03) ------------------------------------------------------------


SYSTEM

 구분 

변환한
DATE

수식보기

   날짜  

2004    1/27  

 무식한
방법 

2004-01-27 =DATE(A3,TRIM(LEFT(B3,FIND("/",B3)-1)),MID(B3,FIND("/",B3)+1,LEN(B3)))
2008    6/ 2   2008-06-02 =DATE(A4,TRIM(LEFT(B4,FIND("/",B4)-1)),MID(B4,FIND("/",B4)+1,LEN(B4)))
2009   12/30   2009-12-30 =DATE(A5,TRIM(LEFT(B5,FIND("/",B5)-1)),MID(B5,FIND("/",B5)+1,LEN(B5)))
2004    1/27  

 SMART한 방법 

2004-01-27 =VALUE(A6&"/"&B6)
2008    6/ 2   2008-06-02 =VALUE(A7&"/"&B7)
2009   12/30   2009-12-30 =VALUE(A8&"/"&B8)


댓글 '2'

[레벨:1]알파라라라라

2012.12.20 17:19:31
*.244.225.242

와~~~ 진짜 감사합니다~~

 

무식하게 하고 있던 제 자신이 부끄러워지네요 ㅠㅠ

profile

[레벨:30]아별

2013.01.02 16:03:48
*.104.126.21

하하.. 저도 무식한 방법을 뗀지 얼마 안되요~ ㅎㅎ

엑셀은 배우면 배울 수록 신기한 툴인 것 같습니다. ^^

 

함께 공부해보아요~~ ㅎㅎ

문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수sort
공지 엑셀일반 엑셀 짜투리 팁 모음.. imagefile [11] [레벨:30]아별 2005-07-22 20467
공지 엑셀일반 엑셀 잘 하는 요령은? _ 엑셀 추천사이트 포함 [2] [레벨:30]아별 2009-11-12 7124
공지 엑셀일반 엑셀 단축키(바로가는키, ShortCut) 총정리입니다. imagefile [164] [레벨:30]아별 2010-08-29 26255
66 엑셀일반 엑셀에서 날짜와 시간 계산하는 법 image [레벨:30]아별 2008-06-27 15082
65 엑셀일반 [펌] sumproduct 함수를 이용한 다중 조건 계산 imagefile [2] [레벨:30]아별 2008-04-11 11991
64 엑셀일반 셀 색상별 합계 구하기( GET.CELL( TYPE,REFERENCE) ) imagefile [레벨:30]아별 2008-03-12 11842
63 엑셀일반 엑셀에서 줄바꿈 한 셀의 줄간격을 조절하기 [레벨:30]a☆ 2004-11-15 9733
62 엑셀일반 엑셀에서 다른 파일/시트 참조하기/영역설정하기 [레벨:30]a☆ 2004-10-15 9043
61 엑셀일반 PDF파일을 EXCEL파일로 변환하기 file [1] [레벨:30]아별 2008-10-24 8869
» 엑셀일반 엑셀에서 날짜 데이터 가공/계산/변환 등 종합 선물 세트. file [2] [레벨:30]아별 2008-06-30 8490
59 엑셀일반 엑셀2003 단축키 모음 (Exel ShortCuts)-- file [레벨:30]아별 2008-10-29 7073
58 엑셀일반 유효성 검사를 이용하여 대분류와 소분류의 연동 목록 작성하기 imagefile [3] [레벨:30]아별 2007-01-07 6875
57 엑셀일반 엑셀에서 시간차이 구하기 imagefile [레벨:30]아별 2008-08-29 6630
56 엑셀일반 [링크] 아이엑셀러 강좌 다운로드 링크입니다. file [레벨:30]아별 2009-12-06 6450
55 엑셀일반 인쇄 설정 팁.. 머리글, 바닥글에 페이지 번호 설정하는 방법 imagefile [2] [레벨:30]아별 2011-08-03 6364
54 엑셀일반 두가지 조건을 만족하는 값 찾기 (직급별 연봉) imagefile [레벨:30]아별 2007-01-02 6327
53 엑셀일반 엑셀에서 유용한 단축키들.. [레벨:30]아별 2005-05-23 6293
52 API Win32 API 를 공부하기 시작해봅시당~ 꺄~ >.< file [레벨:30]아별 2009-11-21 6281
51 엑셀일반 젠쟝 2010.. 사라져 버린 Ctrl+Shift+0 [레벨:30]아별 2011-02-18 6215
50 엑셀일반 숫자를 한자, 한글, 영문으로 변환하여 표시하기. imagefile [레벨:30]아별 2008-08-21 5963