Please Enable JavaScript!
Gon[ Enable JavaScript ]

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

엑셀(Excel)/Excel
반응형

엑셀로 자신만의 달력을 만들어 보겠습니다. 괜찮은 일정관리 툴이 많아서 엑셀을 사용하지 않겠지만 어떻게 적성하는지 공부해 두면 많은 도움이 될 겁니다. 달력을 만들면서 들어간 기능들은 이름 정의”, “데이터 유효성 검사”, “사용자 지정 서식”, “새 서식 규칙등 입니다. 엑셀의 주요 기능들을 한 번에 연습해 볼 수 있습니다.

 

# 달력 샘플 파일 링크 : 

엑셀_달력샘플.xlsx
다운로드

 

 

¤ 년도와 월 선택을 위한 목록 만들기

 

 

먼저 이름 정의기능을 이용해서 달력을 선택할 수 있는 년도와 월 콤보박스를 만들어 보겠습니다. 개발 도구에서 컨트롤을 추가해도 되지만 이름 정의를 사용하시면 간단하게 만들 수 있고 관리도 쉽습니다. 이름 정의를 하기 전에 콤보박스에 들어갈 데이터를 만들어야 합니다. 년도와 월 데이터를 다른 시트에 만듭니다. 그리고 [수식] 탭의 [정의된 이름] 그룹으로 가서 [이름 정의] 리본메뉴를 선택합니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

이름 관리자 창이 뜨면 년도와 월에 해당하는 영역을 선택한 뒤 [새로 만들기] 버튼을 클릭해서 두 개의 이름을 만듭니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

다음은 달력이 만들어 질 시트로 갑니다. 콤보박스를 만들기 위해 [데이터] 탭의 [데이터 도구] 그룹으로 가서 [데이터 유효성 검사] 리본메뉴를 선택합니다. 그럼 [데이터 유효성] 설정을 위한 팝업창이 뜹니다. 팝업창에서 [제한 대상][목록]으로 선택하고 [원본] 값에 위에서 만든 이름을 입력합니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

아래 그림처럼 원본 값에 이름 정의로 만든 이름을 넣으면 데이터 값이 모두 들어간 콤보박스가 만들어 집니다. 콤보박스의 화살표는 셀을 선택하면 나타나고 포커스 다른 곳으로 가면 사라집니다. 이렇게 년도와 월 값을 선택하면 아래 표는 해당하는 달력의 날짜를 표시하는 것입니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※
엑셀 Excel 새해 달력 쉽게 만들기
엑셀 Excel 지정한 달 마지막 날짜 EOMONTH 함수 사용하기
엑셀(Excel) 날짜를 보고 해당 연도가 윤년인지 알아내는 방법
엑셀 Excel 함수 MATCH, INDEX 를 이용해서 행, 열에 일치 데이터 찾기
엑셀 디데이 D-DAY 계산하는 방법날짜 함수 이용

 



 

¤ 달력 만들기

 

 

그럼 년도와 월을 선택했을 때 표에 데이터를 표시하기 위한 수식을 만들어 보겠습니다. 함수는 두 가지가 사용되었습니다. DATE 는 년도와 월, 일 값을 넣으면 날짜 형식을 리턴 합니다. WEEKDAY 는 요일값을 리턴 하는데 해당 년도와 월의 1일 에서 빼 주면 일요일의 날짜를 구할 수 있습니다. 달력의 첫 셀은 일요일 부터 이기 때문에 1일이 무슨 요일인지 알면 날짜 연산에 의해서 일요일 날짜가 구해 지는 것입니다. 여기에 끝에 1을 더하는 것은 요일 값이 하나 모자라기 때문입니다. 이렇게 첫 셀을 만들면 나머지는 쉽게 연산이 가능합니다. 날짜를 더 해주기만 하면 되거든요. 

=DATE(B2,E2,1)-WEEKDAY(DATE(B2,E2,1)) + 1

 

 

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

첫 번째 일요일에 해당하는 셀이 구해 지고 나면 해당 셀을 기준으로 날짜를 더해 갑니다. 날짜 연산이 되기 때문에 계속해서 1 씩 더해 가면 자동으로 월과 년도가 계산이 됩니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

가로에 값을 채웠다면 새로도 드래그해서 값을 채웁니다. 그렇게 해서 모든 셀에 날짜를 채워 넣습니다. 그리고 상단에 년도와 월을 변경하게 되면 아래 표의 값은 자동으로 변경이 됩니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

¤ 사용자 정의 서식으로 일자만 추출하기

 

 

이제 년도, , 일 이 모두 표현된 날짜 형식을 만 보일 수 있도록 변경해 보겠습니다. Ctrl + 1 을 눌러 [셀 서식] 대화상자를 띄웁니다. 그리고 사용자 지정 서식으로 가서 형식을 “d” 로 바꿉니다. 잘 아시겠지만 년도는 “yyyy”, 월은 “mm”, 일은 “d” 로 표기합니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

그럼 아래 그림과 같이 선택한 영역의 날짜는 로 전부 변경이 됩니다. 마지막으로 정말 달력처럼 보이려면 해당 년도와 월에 날짜가 아니면 흐리게 표시해야겠죠.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

¤ 조건부 서식으로 해당 월에 날짜가 아닐 때 흐리게 표시와 토,일 색 구분

 

 

해당 년도와 월에 날짜가 아니면 흐리게 표시하기 위해 [조건부 서식]을 사용하였습니다. 규칙을 만들기 위해 [] 탭의 [조건부 서식] 리본메뉴를 클릭해서 [새 규칙]을 선택합니다.

엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

 

[새 서식 규칙] 대화상자에서 유형을 수식을 사용하여 서식을 지정할 셀 결정으로 선택하고 아래에 식을 입력합니다. 식에서 쓰인 MONTH 함수는 해당 날짜의 월 값을 추출해 줍니다. 상단에서 선택한 월 값과 비교해서 같지 않으면 “<>” 지정한 서식으로 변경하는 것입니다. 그러니까 아래 수식이 참이 되면 서식이 적용되는 것이죠. 그리고 월을 나타내는 $E$2 는 변하지 않는 위치이기 때문에 절대값을 적용한 것입니다.

 

=MONTH(B5)<>$E$2

 

엑셀 Excel 달력 만드는 방법&#44; 날짜 관련 함수 이용

 

위에서 만든 규칙이 참일 때 날짜 값에 적용할 서식을 지정합니다. 흐리게 표시하기 위해서 색을 회색으로 지정했습니다.

엑셀 Excel 달력 만드는 방법&#44; 날짜 관련 함수 이용

 

서식을 지정하고 새로운 규칙을 달력에 적용하시면 아래 그림과 같이 선택한 년도와 월에 날짜가 아니면 흐리게 표시 됩니다.

엑셀 Excel 달력 만드는 방법&#44; 날짜 관련 함수 이용

아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※
엑셀 Excel 새해 달력 쉽게 만들기
엑셀 Excel 지정한 달 마지막 날짜 EOMONTH 함수 사용하기
엑셀(Excel) 날짜를 보고 해당 연도가 윤년인지 알아내는 방법
엑셀 Excel 함수 MATCH, INDEX 를 이용해서 행, 열에 일치 데이터 찾기
엑셀 디데이 D-DAY 계산하는 방법날짜 함수 이용
반응형
Posted by 녹두장군

댓글을 달아 주세요

  1. 배움 2017.07.31 15:56  댓글주소  수정/삭제  댓글쓰기

    잘 배우고 갑니다. 요긴하게 잘 쓸것같아요.

  2. 전성욱 2017.08.31 08:18  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 다름이 아니오라 엑셀 달력 만들기 하실때 일요 = DATE(2017,8,1) - WEEKDAY(DATE(2017,8,1),1) + 1 여기서 1를 더하는 이유가 머에요?

  3. 이호승 2018.03.06 16:13  댓글주소  수정/삭제  댓글쓰기

    와.... 설명은 진짜 잘해주신거 같은데 안만들어져요 굉장히 어렵네요
    엑셀 완전 초보는 따라하기 힘들것 같아요 ㅜㅜ 저 달력 성공하면
    고대로의 모양에다가 밑에 한칸씩 더 넣어서 pc 다이어리로 쓸려고했는데
    실패했네요 ㅠㅠ 년도, 월 은 되는데 날짜함수 데이트랑 위크데이 넣는데 안되요
    엑셀 2013입니다

  4. tj 2018.07.14 13:02  댓글주소  수정/삭제  댓글쓰기

    근데 요일 토요일 하고 일요일 색상변경 설명도 ㅠㅠ

  5. ㅂㄷㄱ 2018.08.29 09:45  댓글주소  수정/삭제  댓글쓰기

    덕분에 정말 멋지게 하나 만들었습니다. 감사해요!!!

  6. 송경근 2019.04.29 16:32  댓글주소  수정/삭제  댓글쓰기

    항상 고개가 숙여 집니다.
    또, 감사히 배우고 갑니다.

    감사합니다.

  7. 여우 2021.06.22 15:11  댓글주소  수정/삭제  댓글쓰기

    상세 설명 해주셔서 깔끔하게 달력 만들 수 있겠네요
    감사합니다

  8. 레몬2 2022.04.26 09:00  댓글주소  수정/삭제  댓글쓰기

    엑셀에 기준날짜를 두고 예를들면 4숼26일 기준 2달 뒤 ㅡ렇게 세팅이 될까요?(에를들면 2달후 6월26일) 이방법을 찾아 헤매는 중밉니다.ㅠ 도와주세요