출처 : 아별닷컴(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셀이다.
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) |
SYSTEM |
구분 |
변환한 |
수식보기 | |
년 |
날짜 | |||
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) |