엑셀 달력, 공휴일 및 일정 관리 자동달력/만년달력 만들기

나만의 휴일, 공휴일을 입력할 수 있는 엑셀 자동화 만년달력 제작 방법과 관련 기초이론을 단계별로 살펴봅니다.

홈페이지 » 엑셀 달력, 공휴일 및 일정 관리 자동달력/만년달력 만들기

엑셀 달력, 공휴일 및 일정관리 가능한 달력 만들기

엑셀 달력 목차 바로가기

엑셀 달력 만들기, 어렵지 않습니다. 간단한 함수 몇가지와 조건부서식을 응용하면 공휴일, 일정이 자동으로 업데이트 되는 만년달력, 자동달력을 누구나 손쉽게 만들 수 있습니다. 이번 강의에서는 엑셀 자동달력 만드는 과정을 단계별로 알아보겠습니다.

엑셀 자동 달력 만들기
이번 강의에서는 엑셀 달력 만드는 과정을 단계별로 알아봅니다.
패치노트
  • 2019년 1월 15일 :
    일요일부터 시작하는 달력 만들기 관련 내용을 추가하였습니다.
  • 2019년 11월 12일 :
    연간 달력을 추가하였습니다.
  • 2020년 3월 8일 :
    익월 표시 달력의 수식을 수정하였습니다. (12월에서+1 더할시 13으로 계산되어 발생되는 오류 수정)

    =IFERROR(IF(IF('달력'!$S$2+1<>MONTH(셀+1),"",셀+1),"")
    =IFERROR(IF(IF(('달력'!$S$2+1)>12,1,'달력'!$S$2+1)<>MONTH(셀+1),"",셀+1),"")
    '// 12월에서 '+1'을 더해 13이 될 경우, 1월 반환합니다. 
영상강의

예제파일 다운로드

관련 기초함수
연/월/일 값을 받아 해당 날짜를 나타내는 일련번호로 반환합니다.
선택한 범위의 열 개수(넓이)를 반환합니다.

엑셀 달력 자동화 양식 다운로드

더욱 많은 기능이 포함된 엑셀 달력 자동화양식을 무료 서식 페이지에 올려드렸습니다. 아래 링크를 확인해주세요.

엑셀 달력, 날짜 계산을 위한 함수 알아보기

달력 첫째주 날짜  함수공식
= COLUMNS(열범위)/2 + DATE(년,월,1) - WEEKDAY(DATE(년,월,1),2)
함수 함수 설명
COLUMNS 함수 입력한 범위의 열개수를 반환합니다.
DATE 함수 년/월/일 값을 받아 날짜로 반환합니다.
WEEKDAY 함수 선택한 날짜의 요일을 지정한 형식의 일련번호로 반환합니다. (예: 일요일=1, 월요일=2, 등...)
예제파일에서 사용된 함수
=COLUMNS($A$6:F$6)/2+DATE($Q$2,$S$2,1)-WEEKDAY(DATE($Q$2,$S$2,1),2)
  1. COLUMNS($A$6:F$6)
    '// A6~F6 범위의 열 개수를 반환합니다.
    = 6
  2. DATE($Q$2,$S$2,1)
    '// Q2셀 : 년도를 받아옵니다
    '// S2셀 : 월을 받아옵니다.
    = 선택한 년도, 월의 1일을 출력합니다.
  3. WEEKDAY(DATE($Q$2,$S$2,1),2)
    '// 매월 1일의 요일번호를 반환합니다. WEEKDAY의 인수로 '2'가 입력되었습니다.
    = 월요일(1) ~ 일요일(7) 까지 요일번호를 반환합니다.
  4. = COLUMNS($A$6:F$6)/2+DATE($Q$2,$S$2,1)-WEEKDAY(DATE($Q$2,$S$2,1),2)
    = 6/2 + ‘2019/2/1’ – WEEKDAY(‘2019/2/1’,2)
    = 3 + ‘2019/2/1’ – 5
    = ‘2019/2/1’ – 2
    = ‘2019/1/30’
달력 둘째주 날짜 표시를 위한 함수공식
= 이전셀 + 1
'// 이전 날짜에 +1 을 순차적으로 더해 달력을 완성합니다.

달력에 공휴일 표시하기

해당 날짜가 휴일일 경우, 휴일 설명을 불러오기 위한 함수
=IFERROR(VLOOKUP(셀,휴일!$A:$B,2,0),"")
함수 함수 설명
IFERROR 함수 입력한 수식이 오류를 반환할 경우, 오류대신 특정 값을 출력합니다.
VLOOKUP 함수 범위의 맨 좌측열에서 값을 찾은 뒤, 옆에 있는 다른 값을 출력합니다. 만약 범위에 찾으려는 값이 없을 경우 #N/A! 오류를 반환합니다.
달력 예제파일에서 사용된 함수
=IFERROR(VLOOKUP( 2019-01-02,휴일!$A:$B,2,0),"")
  1. = VLOOKUP( 2019-01-02, 휴일!$A$B,2,0 )
    '// 2019-01-02 를 휴일시트의 A열에서 검색한 뒤, 값이 있으면 같은 행 B열에 있는 값을 출력합니다.
    '// A 열에 2019-01-02 이 없을 경우, #N/A! 오류를 반환합니다.
    = #N/A!
  2. = IFERROR ( #N/A, “”) :
    '// VLOOKUP 함수에서 #N/A 오류가 반환될 경우 IFERROR 함수를 통해 빈칸을 출력합니다.
    = ""

달력 토/일/공휴일 색상구분을 위한 조건부서식 설정

자동 달력 조건부서식 설정하기

본 예제파일에는 2개의 조건부서식이 미리 적용되어 있으며, 모든 서식에는 동일한 원리의 수식이 사용되었습니다. 따라서 아래 예제만 이해하시면, 엑셀 달력에 적용된 나머지 조건부서식은 쉽게 이해하실 수 있습니다.

  1. 엑셀 달력의 범위를 B6부터 N17까지 선택합니다. 단, 기준셀이 B6셀이 되도록 반드시 B6셀을 시작으로 범위를 선택합니다.

    엑셀 달력 조건부서식 범위 선택
    달력시트의 B6:N17 까지 달력범위를 선택합니다.
  2. [홈] - [조건부서식] - [새규칙]으로 이동합니다.

    달력 조건부서식 새규칙
    조건부서식의 새규칙으로 이동합니다.
  3. '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 뒤, 아래 수식을 복사/붙여넣기합니다. 이후 [서식]을 클릭하여 달력의 조건부서식을 변경합니다.
    =AND(MONTH(B6)<>$S$2,B6<>"")

    달력 새규칙 서식 입력
    수식을 붙여넣기 한 뒤, [서식] 버튼을 클릭합니다.
  4. '글꼴' 로 이동합니다. 이후 색상을 옅은 회색을 선택한 뒤, [확인] 버튼을 눌러 서식설정을 마무리합니다.

    회색 글꼴 적용하기
    글꼴 색상을 옅은회색으로 선택한 뒤, 확인을 눌러 마무리합니다.
  5. 방금 적용한 조건부서식은, 해당셀이 빈칸이 아니고 기준월과 다를경우, 즉 기준월이 아닌 전달 또는 다음달일 경우, 회색으로 글꼴을 적용하는 조건부서식입니다.
    =AND(MONTH(B6)<>$S$2,B6<>"")
    '// MONTH(B6) <> $S$2 : 해당셀이 기준월과 다르고
    '// B6<>"" : 해당셀이 빈칸이 아닐 경우 조건부서식을 적용합니다.

연결될 이미지로 엑셀 자동달력 꾸미기

연결된 그림 기능은 엑셀 2007 이후 버전에서 지원되는 기능입니다.

  1. [연결이미지] 시트로 이동한 뒤, 달력 제목을 표시하기 위한 P2:S2 범위를 복사합니다.

    달력 머릿글 범위 복사
    연결이미지 시트의 P2:S2 범위를 복사합니다.
  2. [달력] 시트로 이동합니다. J2 셀을 우클릭한 뒤, '선택하여 붙여넣기' - '연결된 그림' 으로 붙여넣기합니다.

    연결된 그림 붙여넣기
    복사한 범위를 '연결된 그림' 형태로 붙여넣기합니다.
  3. [연결이미지] 시트의 B2:N10 범위도 복사한 뒤, [달력] 시트 B18셀에 연결된 그림으로 붙여넣기합니다.

    엑셀 익월 달력 추가 완료
    '연결이미지' 시트의 달력범위도 복사한 뒤, 연결된그림으로 붙여넣기합니다.

자주묻는질문

Q1. 일요일부터 시작하는 달력을 만들고 싶어요

달력 첫째주 날짜를 입력하는 공식의 WEEKDAY 함수 두번째 인수만 변경하면, 일요일부터 시작하는 달력으로 손쉽게 변경할 수 있습니다. 공식은 아래와 같습니다.

=COLUMNS(열범위)/2+DATE(년,월,1)-WEEKDAY(DATE(년,월,1),1)
  • 위 공식에서 WEEKDAY 함수의 두번째 인수를 '1'로 변경하면, 반환되는 일련번호가 일요일(1) ~ 월요일(7)로 변경됩니다.
  • 달력 토/일요일의 서식 색상을 변경하면 아래와 같이 일요일부터 시작하는 달력이 간단하게 완성됩니다.

    달력 휴일 색상 변경
    토/일요일의 글꼴 색상을 변경하여 일요일 시작하는 달력을 완성합니다.
Q2. 엑셀 연간 만년달력을 만들고 싶어요

달력 날짜계산에 사용된 함수를 복사/붙여넣기하면 연간달력 또한 손쉽게 만들 수 있습니다. 제작된 만년달력은 완성파일 달력(연간) 시트에 같이 첨부하여 드렸습니다.

엑셀 달력 만년달력 만들기
날짜계산에 사용된 함수를 복사/붙여넣기 하면 연간달력또한 손쉽게 만들 수 있습니다.
4.8 21 투표
게시글평점
guest
33 댓글
Inline Feedbacks
모든 댓글 보기
leena
leena
2019년 12월 24일 3:47 오전

썜 괄호하나 빠져써여
= AND(MONTH(B6)<>$S$2),B6<>””) ->= AND((MONTH(B6)<>$S$2),B6<>””)

조용은
조용은
2020년 1월 7일 1:45 오후
게시글평점 :
     

안녕하세요 강의 보고 궁금한 점이 있어서 문의 드려요 첫째주 날짜입력방식의 함수 사용을 왜 저렇게 표현해서 값을 구하는지 이해가 되지 않아서 문의 드려요 다른 방식으로 표현했을 때 작동이 안되거나 문제가 생기는지… 더보기 »

박낸낸
박낸낸
2020년 1월 14일 1:16 오후
게시글평점 :
     

회사에 입사하면서 엑셀을 여기서 보고 배우면 좋다고 들어 왔는데 정말 차근차근 잘 알아갈 수 있어 너무 좋네요 ! 이런 좋은 정보 감사합니다.

정지윤
정지윤
2020년 2월 1일 10:13 오후
게시글평점 :
     

안녕하세요! 강의 잘 들었습니다. 질문이 있는데, 혹시 같은 날짜에 두개 이상의 일정이 있을 시에는, 동시에 이 두 일정을 표시하지 못하는것인가요? 엑셀 시트 상 더 상단에 작성된 일정만 표시가 되거든요. 혹시… 더보기 »

l804325
l804325
2020년 2월 10일 10:39 오전
게시글평점 :
     

선생님, 달력에 빈칸을 추가해서 일정을 기입했는데 다른 달에도 동일한 일정으로 계속 반영되는데 다른달에 반영안되고 빈칸으로 하는 방법있을까요? (예, 2월1일 00생일- > 3월1일 00생일 -> 4월1일00생일...)

소나로사
소나로사
2020년 3월 8일 4:02 오후
게시글평점 :
     

강의 잘 보았습니다. 그리고 공부도 많이 되었습니다. 한가지 옥에 티가 있어서요. 매년 12월일때 다음해 1월 달력이 3째주부터 나오질 않아서 나름 수정을 해보았습니다. =IFERROR(IF((달력!$S$2+1)MONTH(N7+1),"",N7+1),"") 을 아래와 같이 =IFERROR(IF(DAY(N7+1)=DAY(1),"",IF(달력!$S$212,IF((달력!$S$2+1)=MONTH(N7),N7+1,""),IF((달력!$S$2-11)=MONTH(N7),N7+1,""))),"") 작동은 잘 되는데요(고민… 더보기 »

소나로사
소나로사
2020년 3월 9일 4:43 오전
답글 남기기  오빠두엑셀

감사합니다. 강의를 보면서 많은걸 배웁니다

소나로사
소나로사
2020년 3월 9일 5:49 오전
답글 남기기  오빠두엑셀

강제로 1로 바꿔주는 것을 생각 못했네요,,^^ 감사합니다

김성규
김성규
2020년 3월 12일 12:08 오후
게시글평점 :
     

정리가 잘되어있어서 좋네영

호야
호야
2020년 3월 15일 12:43 오전
게시글평점 :
     

어려워지네요 잘보고 갑니다

excellguy
excellguy
2020년 3월 20일 12:37 오후
게시글평점 :
     

와우~! 어렵지만. . 매력 있습니다~!

말리노
말리노
2020년 3월 24일 11:04 오전
게시글평점 :
     

안녕하세요. 강의 감사합니다.

강의 중에
=NOT(ISERROR(VLOOKUP(셀,휴일!$A:$A,1,0)))
함수를 사용하여 휴일을 빨갛게 표시하는 조건부 서식을 사용하셨는데
이때 왜
=VLOOKUP(셀,휴일!$A:$A,1,0)
함수를 사용하지 않으신건지 궁금합니다.

크리슈
크리슈
2020년 3월 29일 3:03 오후
게시글평점 :
     

달력 너무 너무 신기합니다.. 자동화 달력 너무 받고 싶어요 ㅠ

노은
노은
2020년 3월 30일 9:45 오후
게시글평점 :
     

일정관리하기에 유용한것 같아요

angami919
angami919
2020년 3월 31일 11:09 오전
게시글평점 :
     

정말 딱 필요한거에요!!!!!

엑린이
엑린이
2020년 4월 10일 10:54 오후
게시글평점 :
     

좋은 강의 배웁니다

스윙
2020년 4월 29일 11:26 오후
게시글평점 :
     

안녕하세요.. 달력 아주잘 쓰고 있습니다..
궁금한점은 일반 일정에 2가지가 겹치면
2개까지는 표시해주는걸로 알고있는데..
3개일때는 1번일정과 3번일정이표시되고..
2개일정일때는 1개만 표시가 되네요..
어케 해야하나요...

올챙이
올챙이
2020년 5월 14일 6:39 오후
게시글평점 :
     

앗... 5강이 어려워 졌다. 하지만, 수강... 따라 했는데, 이해하는데 시간이 걸리네요. 하지만, 재미있네요.

only****
2020년 5월 18일 9:59 오후
게시글평점 :
     

이건 정말 엄두가 안 나네요..ㅎㅎㅎ 아무튼 좋은 강의 진심 감사합니다.

아르드
아르드
2020년 5월 24일 12:37 오전
게시글평점 :
     

재밌고 실전에 사용할 수 있게 강의해 주셔서 감사합니다.

초코우유
초코우유
2020년 6월 14일 7:33 오후
게시글평점 :
     

정말 양질의 강의에요
지금 저의 단계에서는 어렵지만 열심히 배워서 다시 봐야겠어요!

곰고미
곰고미
2020년 7월 13일 2:21 오전
게시글평점 :
     

좋은강의 감사합니다~^^

주뽕
주뽕
2020년 8월 13일 11:25 오전
게시글평점 :
     

만년달력 보면서 공부하고있습니다. 궁금한점이 있습니다. 일정에서 색상선택하면 빨간바탕에 흰색글씨로 보여집니다. 이걸 흰색바탕에 빨간색으로 보여질수있게 할수있는지요

찰스
찰스
2020년 9월 22일 7:13 오전
게시글평점 :
     

안녕하세요, 좋은 강의들 잘 보고 있습니다. 위 내용 중에 날짜(일자)에 대한 셀 서식 "d"사용 관련 내용이 없는것 같습니다 ~ 수식 적용 후 일자에 대한 내용만 보여주기 위해 'd'서식이 사용되었음을 표시해주시면… 더보기 »

h****
h****
2020년 10월 19일 10:09 오전
게시글평점 :
     

좋은 강의 감사합니다.

33
0
여러분의 생각을 댓글로 남겨주세요.x