엑셀 날짜 주 단위 계산 4가지 상황별 완벽 정리

엑셀 날짜 주 단위 변환 마스터 공식

각 상황별로 적용할 수 있는 4가지 주 단위 변환 마스터 공식은 다음과 같습니다.

1. 연 기준 주 단위 계산

   =WEEKNUM(날짜,기준)

2. 월 기준 주 단위 계산

   =WEEKNUM(날짜,기준)-WEEKNUM(EOMONTH(날짜,-1)+1,기준)+1

3. 매월 특정 요일 기준 주 단위 계산

   =WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,기준)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10)

4. 매월 1일 기준 주 단위 계산

   =IF(OR(WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,기준)+1))+7)<WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,기준)+1))),WEEKNUM(날짜,기준)=WEEKNUM(EOMONTH(날짜,0)+1,기준)),1,WEEKNUM(날짜,기준)-WEEKNUM(EOMONTH(날짜,-1)+1,기준)+1)

5. 매월 1일 기준 년 단위 계산 관련 엑셀 Q&A 게시글 링크

   =IF(IF(WEEKDAY(DATE(YEAR(날짜),1,1),기준)<>7,WEEKNUM(날짜)-1,WEEKNUM(날짜))=0,IF(WEEKDAY(DATE(YEAR(날짜)-1,1,1),기준)<>7,WEEKNUM(DATE(YEAR(날짜)-1,12,31))-1,WEEKNUM(DATE(YEAR(날짜)-1,1,1))),IF(WEEKDAY(DATE(YEAR(날짜),1,1),기준)<>7,WEEKNUM(날짜)-1,WEEKNUM(날짜)))

각 공식의 동작 원리는 아래 두 게시글에서 단계별로 자세히 설명해 드립니다.

공식에 사용된 인수 설명

기준 요일 번호

국가기술표준원에서 공시한 '주간수 결정법'에 따른 마스터 공식

국가기술표준원에서 공시한 '주간수 결정법'이라는 주 단위 표준 계산법이 별도로 마련되어 있습니다. (정보를 공유해 주신 @가나다라다나가 님께 감사드립니다.)

위 계산법을 정리하면, 주 단위 계산 시 다음 두 가지 규칙이 지켜져야 합니다.

따라서 주간수 결정법을 기준으로 한 마스터 공식은 다음과 같습니다.

=IF(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜),1),11)<=5,IF(AND(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)<=5,WEEKNUM(날짜,11)=WEEKNUM(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)),1,WEEKNUM(날짜,11)-WEEKNUM(EOMONTH(날짜,-1)+1,11)+1),IF(AND(DAY(날짜)<8,WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10)>2,WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)-1,1),2)<=5),WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10)+1,IF(AND(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)<=5,OR(WEEKNUM(날짜,11)=WEEKNUM(DATE(YEAR(날짜),MONTH(날짜)+1,1),11),WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,11)+1))+7)<WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,11)+1))))),1,WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10))))

주간수 결정법에 따라 계산할 때는 전월·다음 달 1일이 무슨 요일인지에 따라 계산 방식이 달라지기 때문에 공식이 다소 길어집니다. 각 구간별로 나눠 계산 흐름을 정리하면 다음과 같습니다.

대분류1: 이번달 1일이 '월~목'에 포함되는 경우 (--> 기존 방식으로 주 번호 계산)

1. [중분류1] 다음달 1일이 '월~목'이면서 오늘 날짜의 연주차가 다음달 1일의 연주차와 동일한 경우
   ==> 1
   IF(AND(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)<=5,WEEKNUM(날짜,11)=WEEKNUM(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)),1,...
2. [중분류2] 그렇지 않을 경우
   ==> 주 번호 계산
   WEEKNUM(날짜,11)-WEEKNUM(EOMONTH(날짜,-1)+1,11)+1)

대분류2: 이번달 1일이 '금~일'인 경우 (--> 월요일 시작 기준 주 번호 계산)

1. [중분류1] 오늘이 8일 이전이면서, 월요일 시작 기준 주 번호가 2보다 크고, 이전달 1일이 '월~목'인 경우 (⭐ 즉, 오늘 날짜가 이번 달 끝자락이지만 월요일 기준으로 계산하면 다음달 1주차로 포함되는 경우)
   ==> 월요일 기준 주 번호 + 1 (이전달 1일이 '월~목'이므로 기존 주 번호에 1을 더해 보정합니다.)
   IF(AND(DAY(날짜)<8,WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10)>2,WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)-1,1),2)<=5),WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10)+1,...
2. [중분류2] 다음달 1일이 '월~목'이면서 오늘 날짜의 연주차가 다음달 1일의 연주차와 동일하거나, 다음 주 연주차가 오늘 날짜의 연주차보다 클 경우 (* 12월에서 1월로 넘어가는 주에 해당)
   ==> 1
   IF(AND(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)<=5,OR(WEEKNUM(날짜,11)=WEEKNUM(DATE(YEAR(날짜),MONTH(날짜)+1,1),11),WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,11)+1))+7)<WEEKNUM(DATE(YEAR(날짜),MONTH(날짜),DAY(날짜-WEEKDAY(날짜,11)+1))))),1,...
3. [중분류3] 그렇지 않을 경우
   ==> 월요일 시작 기준 주 번호 계산
   WEEKNUM(DATE(YEAR(날짜),1,DAY(날짜-WEEKDAY(날짜,11)+1)),WEEKDAY(DATE(YEAR(날짜),1,1),11)+10))))

엑셀 주 단위 계산 - 기초 (년기준 vs 월기준)

엑셀에서 날짜를 주 단위로 변환하는 방식은 크게 두 가지로 나눌 수 있습니다.

1년 기준으로 주 단위를 계산하는 방식을 달력으로 표현하면 다음과 같습니다.

실제 업무에서는 1년 기준보다 '월 기준'으로 주 단위를 계산하는 경우가 더 많습니다. 월 기준 계산을 달력으로 표현하면 다음과 같습니다.

다만 이 방식으로 매월 주 단위를 계산할 경우 상황에 따라 정확하게 계산되지 않을 수 있습니다. 한 주 안에 7일이 온전히 포함되지 않는 구간이 발생하기 때문입니다. 예를 들어 2019년 12월의 마지막 주와 2020년 1월의 첫째 주를 보면, 12월 마지막 주에는 3일만, 1월 첫째 주에는 4일만 포함된 것을 확인할 수 있습니다.

이러한 문제를 해결하기 위해 월 기준 주 단위 계산 시에는 두 가지 대안을 고려할 수 있습니다.

월 기준 주 단위 계산 문제 해결 방법

기존 월 기준 주 단위 변환에서 발생하는 '한 주 안에 7일이 온전히 들어가지 않는 문제'를 해결하기 위해 다음 두 가지 방법을 고려할 수 있습니다.

각 방법이 어떻게 적용되는지 실제 달력과 함께 살펴보면 다음과 같습니다.

특정 요일 기준 주 단위 계산 예제

특정 요일 기준으로 주 단위를 계산하면 2020년 1월 ~ 2020년 2월의 주 번호는 다음과 같이 산출됩니다.
(* 예제에서는 '일요일'을 기준으로 주 단위를 계산했습니다.)

이처럼 특정 요일 기준으로 주 단위를 계산하면, 1월의 첫 일요일이 1/5이므로 1월의 첫째 주는 1월 5일부터 시작합니다. 동일하게 2월도 첫 일요일이 2/2이므로 2월 2일부터 첫째 주로 계산됩니다.

매월 1일 기준 주 단위 계산 예제

특정 요일 기준이 아닌 매월 1일 기준으로 주 단위를 계산할 수도 있습니다. 매월 1일 기준 계산은 전월의 마지막 날이 당월 첫째 주에 포함되는 방식이며, 달력으로 살펴보면 다음과 같습니다.

매월 1일 기준으로 주 단위를 계산하면, 매월 1일이 포함된 주를 그 달의 첫째 주로 계산합니다. 따라서 2020년 1월의 경우 전월 마지막 날인 12/29~12/31이 1월 첫째 주에 포함되며, 동일한 원리로 1월 마지막 날인 1/26~1/31도 2월 첫째 주에 포함되어 주 번호가 산출됩니다.

엑셀 날짜 주단위 변환 공식 사용방법

앞서 안내한 마스터 공식은 '찾기 및 바꾸기' 기능을 활용하면 원하는 시트 어디에든 손쉽게 적용할 수 있습니다.

1. 마스터 공식을 복사해 원하는 셀에 붙여넣습니다. 이 단계에서는 함수 결과값으로 #NAME? 오류가 출력되는 것이 정상입니다.
2. 함수가 입력된 셀과 함께 옆의 빈 셀(또는 Ctrl 키를 누른 채 떨어진 빈 셀을 동시 선택)을 함께 선택한 뒤, [홈] - [찾기 및 선택] - [바꾸기] 메뉴로 이동하거나 단축키 Ctrl + H로 '찾기 및 바꾸기'를 실행합니다.
3. [찾을 내용]에 '날짜', [바꿀 내용]에 날짜가 입력된 셀 주소를 입력한 뒤 [모두 바꾸기] 버튼을 클릭합니다. 수식의 '날짜' 텍스트가 해당 셀 주소로 일괄 변경됩니다.
   
4. [찾을 내용]에 '기준', [바꿀 내용]에 적용할 기준 요일 번호를 입력한 뒤 [모두 바꾸기] 버튼을 클릭하면 수식의 '기준'이 해당 숫자로 변경됩니다.
   
5. 이후 수식을 자동 채우기로 복사하면 각 날짜의 주 단위 계산이 완료됩니다.