(엑셀 tip) 매월 마지막 날짜 입력을 간단하게
페이지 정보
본문
엑셀로 표를 작성하다 보면 매월 마지막 날짜를 표에 포함시켜야 하는 경우가 생깁니다.
예를 들어, 매월 1일마다 해당월 1일~말일에 대한 청구서를 엑셀로 작성한다고 가정해 보지요.
첫 칸에는 기간이 들어갑니다 -> 기간 : 2019-4-1 ~ 2019-4-30
4월 1일은 어떻게 입력할까요?
너무 엑셀만 믿지 않는 분들께서는 직접 입력하시겠고 | 2019.04.19 |
함수를 처음 접하시는 분들은 | =today() | 하시겠지만,
반드시 매월 1일날 출근을 한다는 보장이 없으므로 우리는 A1셀에 이렇게 입력해 봅시다
=date(year(today()),month(today()),1)
엑셀 파일을 몇월 몇일에 열든 올해, 이번달의 1일이 자동으로 찍히겠지요?
그렇다면 마지막 날은 어떻게 해야 할까요.
1 대신 30을 넣어야 할까요 아니면 31을?
단순하게 생각하면 매 월마다 30일인지 31인지 표를 만들거나 if문을 써도 될 것 같습니다.
2월이면 28일, 4, 6, 9, 11 월은 30일, 나머지는 31일로 나오게요.
그러다 2020년이 윤년이라는 것을 깨닫게 되면 '괜찮아 4년에 한번은 수기로 입력해도 돼!' 하시려나요? ^^;
그런데 사실 복잡할 것 없습니다.
4월 30일은 5월 0일, 5월 31일은 6월 0일과 같다고 생각하시면 아주 간단해 집니다.
B1셀에는 이렇게 입력해 봅시다
=date(year(today()),month(today())+1,0)
그런데 문서에 따라서는
매월 첫날이 아닌 첫 영업일을 적어야 하는 경우도 생기겠지요?
1일이 토요일이나 일요일일 경우에는 1일이 아닌 2일, 3일로 날짜가 찍히게 하고 싶습니다.
엑셀의 날짜형식은 숫자로 변환되는 것 다들 아시죠?
나누기 7을 했을 때에 나머지가 0이면 토요일, 1이면 일요일, 2이면 월요일 입니다. | =MOD(today(),7) |
나누기를 하는 대신 weekday 함수를 쓰면 더 편리합니다. | =weekday(today()) |
다만 이 경우에는 토요일이 0이 아닌 7로 나옵니다.
따라서 위 A1 셀을 weekday 함수에 넣었을 때 값이 7이 나오면 날짜에 2를, 1이 나오면 날짜에 1을 더하면 되는 겁니다.
if 문을 두 겹으로 써도 되겠지만 더 깔끔하게 하려면 choose 함수를 한번 써볼까요?
A2 셀에는 이번 달의 첫 영업일이 입력되도록 할텐데, A1셀에 이미 적었던 함수를 활용해서 일단 이렇게 해봅시다
=$A$1+choose(weekday($A$1),1,0,0,0,0,0,2)
weekday 를 이용해 구한 요일 값이 1(일요일)일 때에는 날짜에 1을 더하고, 7(토요일)일 때에는 날짜에 2를 더했습니다.
아 그런데, 이달의 첫 영업일을 구하기 위해서 굳이 이 달의 첫 날이 화면에 표시되도록 할 필요가 있을까요?
함수는 조금 길고 지저분해지겠지만 이렇게 하면 한번에 처리가 되겠군요
=date(year(today()),month(today()),choose(weekday(date(year(today()),month(today()),1)),2,1,1,1,1,1,3))
얼핏 보면 굉장히 복잡해 보일 수도 있겠지만 여기까지 스크롤을 그냥 내린 게 아니고 차근차근 읽어오셨다면
맨 처음에 나온 date 함수에서 날짜 부분만 살짝 바꾼 것에 지나지 않습니다.
맨 처음 함수와 괄호 색깔에 주의해서 비교해 보셔요~
아, 그런데 반전이 하나 있습니다.
매월 첫 영업일을 구하기 위해서 함수를 이용해서 주말을 처리했쟎아요?
주말 이외의 공휴일, 명절은 이렇게 한 줄로 처리할 방법이 없어요.
공휴일이나 명절 날짜를 미리 시트 하나로 정리해 놓고 해당 날짜를 검출하는 함수나 VBA를 짤 수는 있겠지만
그러면 배보다 배꼽이 더 커지겠지요. 날짜 입력하는 데에 우리가 그렇게까지 공을 들일 필요는 없으니깐요.
그래서 저는 그냥 이런 식으로 합니다.
인쇄 영역 바깥에 날짜를 직접 적는 칸을 하나 만들어 두고, 월초에 공휴일이 걸릴 때에만 수동으로 날짜를 적어주는 거죠.
그래서 날짜 칸이 비어있을 때에는 위의 함수를 적용하고, 내가 직접 숫자를 적어주면 그 숫자가 날짜가 되도록.
아래는 제가 실제로 사용하는 파일을 캡쳐 뜬 겁니다.
함수로 하면 대충 이렇게 되겠네요
=date(year(today()),month(today()),if(isblank($e$1),choose(weekday(date(year(today()),month(today()),1)),2,1,1,1,1,1,3),$e$1))
이렇게 하면 해당월의 첫 근무일을 따로 지정해주고 싶을 때에는 e1 셀에 숫자를 적고, 그렇지 않을 때에는 e1셀을 비워두면 됩니다.
가끔 지난 달에 숫자 적어둔 것을 이번 달에 지워야 하는 데 까먹는 상황이 생길 수 있으므로 눈에 확 띄도록 서식을 지정해 주세요.
3줄 요약
=date(year(today()),month(today()),1)
=date(year(today()),month(today())+1,0)
=date(year(today()),month(today()),if(isblank($e$1),choose(weekday(date(year(today()),month(today()),1)),2,1,1,1,1,1,3),$e$1))
안졸리니졸려님의 댓글