엑셀 날짜 주 단위 계산 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)

각 공식의 동작원리에 대한 자세한 설명은 아래 두 게시글에서 단계별로 상세히 다뤄드렸습니다.

공식에 사용된 인수 설명
인수 설명
날짜 주 단위를 계산할 날짜가 입력된 셀 또는 날짜입니다. 엑셀에서 날짜로 인식할 수 없는 값이 입력될 경우 공식은 #VALUE! 오류를 반환합니다.
기준 주 단위를 계산할 기준 요일입니다. 기준 요일 번호는 아래 별도의 표로 정리해드렸습니다.
기준 요일 번호
기준요일 번호
11
12
13
14
15
16
17

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

국가기술표준원에서 공시한 "주간수결정법"이라는 주 단위 표준 계산법이 있다고 합니다. (정보를 공유해주신 @가나다라다나가님께 감사드립니다.)

국가기술표준원 주단위계산
국가기술표준원에서 공시한 주간 수 결정방법

위 계산법을 쉽게 정리하면, 주 단위 계산시 아래 2가지 규칙이 지켜져야 합니다.

  1. 한 주는 '월요일 시작'을 기준으로 계산합니다.
  2. 7일 중 과반, 즉 목요일 이후를 포함하는 주를 한 주로 계산합니다. 예를들어 2020년10월1일은 금요일이므로 2020년10월4일 부터 10월 1주차로 계산합니다.

따라서 주간수결정법에 따른 마스터 공식은 아래와 같습니다.

=IF(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜),1),11)<=4,IF(AND(WEEKDAY(DATE(YEAR(날짜),MONTH(날짜)+1,1),11)<=4,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)<=4),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)<=4,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)<=4,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)<=4),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)<=4,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 월기준)

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

  1. 1년 기준으로 주 단위를 계산
  2. 월 기준으로 주 단위를 계산

1년 기준으로 주 단위를 계산하는 것을 달력 위에 표현하면 아래와 같습니다.

주 번호
52 12/22 12/23 12/24 12/25 12/26 12/27 12/28
53 12/29 12/30 12/31
1 1/1 1/2 1/3 1/4
2 1/5 1/6 1/7 1/8 1/9 1/10 1/11
3 1/12 1/13 1/14 1/15 1/16 1/17 1/18
4 1/19 1/20 1/21 1/22 1/23 1/24 1/25
5 1/26 1/27 1/28 1/29 1/30 1/31 2/1
6 2/2 2/3 2/4 2/5 2/6 2/7 2/8
7 2/9 2/10 2/11 ...

하지만 실제 현업에서는 1년 기준보다 주로 '월 기준'으로 주 단위를 계산하는 경우가 많습니다. 월 기준으로 주 단위를 계산하는 것을 달력 위에 표현하면 아래와 같습니다.

주 번호
5 12/22 12/23 12/24 12/25 12/26 12/27 12/28
6 12/29 12/30 12/31
1 1/1 1/2 1/3 1/4
2 1/5 1/6 1/7 1/8 1/9 1/10 1/11
3 1/12 1/13 1/14 1/15 1/16 1/17 1/18
4 1/19 1/20 1/21 1/22 1/23 1/24 1/25
5 1/26 1/27 1/28 1/29 1/30 1/31
1 2/1
2 2/2 2/3 2/4 2/5 2/6 2/7 2/8
3 2/9 2/10 2/11 ...

하지만 이런식으로 매월 주 단위를 계산할 경우 상황에 따라 100% 완벽하게 계산되지 않을 수도 있습니다. 왜냐하면 한 주안에 완벽하게 7일이 들어가지 않는 문제가 발생하기 때문인데요. 예를들어 2019년 12월달의 마지막 주와 2020년 1월달의 첫째주를 보면, 12월 마지막 주에는 3일만 포함되고, 1월 첫째주에는 4일만 포함된 것을 볼 수 있습니다.

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

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

기존의 월 기준 주 단위 변환 시 발생하는 "한 주 안에 완벽하게 7일이 들어가지 않는 문제"를 해결하기 위하여 2가지 방법을 고려할 수 있습니다.

  1. 특정 요일 기준 주 단위 계산
    - 당월 첫째 날이 이전 달 마지막 주로 계산됩니다.
  2. 매월 1일 기준 주 단위 계산
    - 전월 마지막 날이 당월 첫째 주로 계산됩니다.

각 방법이 적용되는 방식을 실제 달력과 함께 살펴보면 아래와 같습니다.

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

특정 요일 기준으로 주 단위를 계산할 경우, 2020년 1월 ~ 2020년 2월의 주 번호는 아래와 같이 계산됩니다.
(*예제에서는 '일요일'을 기준으로 주 단위를 계산하였습니다.)

주 번호
6 12/29 12/30 12/31 1/1 1/2 1/3 1/4
1 1/5 1/6 1/7 1/8 1/9 1/10 1/11
2 1/12 1/13 1/14 1/15 1/16 1/17 1/18
3 1/19 1/20 1/21 1/22 1/23 1/24 1/25
4 1/26 1/27 1/28 1/29 1/30 1/31 2/1
1 2/2 2/3 2/4 2/5 2/6 2/7 2/8
2 2/9 2/10 2/11 ...

위와 같이 특정 요일 기준으로 주 단위를 계산할 경우, 1월 달에서 일요일은 1/5 에 처음 시작되므로, 1월달의 첫째주는 1월 5일부터 시작됩니다. 동일하게 2월달도 첫번째 일요일은 2/2 이므로, 2월 2일부터 첫째주로 계산됩니다.

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

특정 요일 기준이 아닌 매월 1일 기준으로 주 단위를 계산할 수도 있습니다. 매월 1일 기준으로 주 단위를 계산하면 전 달의 마지막 날이 당월 첫째주로 포함됩니다. 이를 달력과 함께 살펴보면 아래와 같습니다.

주 번호
1 12/29 12/30 12/31 1/1 1/2 1/3 1/4
2 1/5 1/6 1/7 1/8 1/9 1/10 1/11
3 1/12 1/13 1/14 1/15 1/16 1/17 1/18
4 1/19 1/20 1/21 1/22 1/23 1/24 1/25
1 1/26 1/27 1/28 1/29 1/30 1/31 2/1
2 2/2 2/3 2/4 2/5 2/6 2/7 2/8
3 2/9 2/10 2/11 ...

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

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

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

  1. 마스터 공식을 복사하여 원하는 셀 안에 붙여넣기 합니다. 함수 결과값으로 #NAME? 오류가 출력되는 것이 정상입니다.

    엑셀 날짜 주 단위 변경 공식 NAME 오류
    날짜 주 단위 변경 공식을 복사/붙여넣기 합니다. (NAME 오류가 출력되는 것이 정상입니다)

  2. 함수가 입력된 셀과 함께 빈셀 (또는 키보드 Ctrl 키를 누른 채 떨어진 빈 셀을 동시 선택)을 동시에 선택한 뒤, [홈] - [찾기 및 선택] - [바꾸기]로 이동하거나 단축키 Ctrl + H 로 찾기 및 바꾸기를 실행합니다.

    찾기 및 바꾸기 실행
    수식이 입력된 셀과 다른 빈 셀을 선택한 뒤, 찾기 및 바꾸기로 이동합니다.

  3. 찾을 내용으로 '날짜', 바꿀 내용으로 날짜가 입력된 셀 주소를 입력한 뒤, [모두 바꾸기] 버튼을 클릭합니다. 수식의 '날짜'가 해당 셀 주소로 변경됩니다.
    엑셀 주 단위 변경 날짜 변경
    찾을 내용 : 날짜, 바꿀내용 : 셀 주소를 입력한 뒤 [모두 바꾸기] 버튼을 클릭합니다.
  4. 찾을 내용으로 '기준', 바꿀 내용으로 주 단위를 변경할 기준 요일 번호를 입력한 뒤, [모두 바꾸기] 버튼을 클릭하면 수식의 '기준'이 해당 숫자로 변경됩니다.
    엑셀 주 단위 변경 기준 요일 입력
    찾을 내용 : 기준, 바꿀내용 : 요일번호를 입력한 뒤, [모두 바꾸기] 버튼을 클릭합니다.
  5. 이후 수식을 자동채우기하면 각 날짜의 주 단위 계산이 완료됩니다.

    엑셀 주 단위 변경 완료
    완성된 수식을 자동채우기 하면 주단위 계산이 완료됩니다.

4.8 4 투표
게시글평점
guest
9 댓글
Inline Feedbacks
모든 댓글 보기
aron007
aron007
2020년 10월 20일 2:20 오후
게시글평점 :
     

많은 도움이 되었습니다 ^^ 감사합니다.

Sam Ryu
Sam Ryu
2021년 7월 5일 7:58 오후
게시글평점 :
     

오빠두님 최고...이보다 완벽할 순 없다.

dark****
dark****
2021년 8월 8일 12:32 오전
게시글평점 :
     

감사합니다. 그런데 7일 중 과반, 즉 목요일 이후를 포함하는 주를 한 주로 하고, 이걸 1년 기준으로 주 단위를 계산하는 공식은 별도로 없을까요?

dark****
dark****
2021년 8월 11일 7:47 오전
답글 남기기  오빠두엑셀

댓글 감사합니다. 근데 죄송하지만 방금해봤는데, 저희 회사 주차 기준이랑은 안맞네요ㅠ 목요일 기준으로 하니 1월1일이 수요일이면 수요일이 1주차가 되고, 목요일이 2주차로 변하네요 연말+연초만 과반기준이고, 한주의 기준은 무조건 월~일로 하게 됩니다. 예로… 더보기 »

dark****
dark****
2021년 8월 11일 7:46 오전
답글 남기기  dark****

.

Last edited 3 월 전 by dark****
illing
illing
2021년 8월 10일 9:06 오전
게시글평점 :
     

오빠두님 연단위로 과반 계산방법은 없을까용??

Good!
Good!
2021년 11월 15일 4:48 오후
게시글평점 :
     

제가 잘못 적용한건지 모르겠으나 2021년 기준 3월 29일, 6월 28일, 8월 30일, 11월 29일이 5주차가 아닌 1주차로 표기됩니다.
잘못 적용한건가요?

Last edited 12 일 전 by Good!
9
0
여러분의 생각을 댓글로 남겨주세요.x