엑셀에서 두 날짜 사이의 특정 요일이 몇 개인지 계산하는 작업은 프로젝트 관리, 근무일 계산, 일정 관리에서 매우 중요합니다. SUMPRODUCT, WEEKDAY, ROW, INDIRECT 함수를 조합하면 복잡한 계산도 간단하게 해결할 수 있습니다. 이 글에서는 실무에서 바로 활용할 수 있는 다양한 공식과 예시를 통해 날짜 계산의 전문가가 되어보겠습니다.
SUMPRODUCT와 WEEKDAY 함수 기본 공식 이해하기
가장 핵심이 되는 공식은 =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B2&”:”&C2)))=D2)*1) 입니다. 이 공식은 시작날짜(B2)와 종료날짜(C2) 사이에서 특정 요일(D2)이 몇 번 나타나는지를 계산합니다. ROW(INDIRECT(B2&”:”&C2)) 부분은 두 날짜 사이의 모든 날짜를 연속된 숫자로 변환하는 역할을 하며, 엑셀에서 날짜가 1900년 1월 1일부터 시작하는 일련번호로 저장되는 특성을 활용합니다. 이 함수 조합에 대한 자세한 설명은 여기서 확인할 수 있습니다.
WEEKDAY 함수의 요일 번호 체계 활용법
WEEKDAY 함수는 날짜를 1부터 7까지의 숫자로 변환합니다. 기본 설정에서는 일요일이 1, 토요일이 7이 됩니다. 하지만 return_type 매개변수를 사용하면 요일 계산 기준을 변경할 수 있습니다. return_type이 2일 때는 월요일이 1이 되고 일요일이 7이 됩니다. 이는 월요일을 주의 시작일로 하는 국제 표준(ISO 8601)과 일치합니다. 예를 들어 =WEEKDAY(TODAY(),2)를 사용하면 오늘이 월요일부터 시작하는 주에서 몇 번째 요일인지 알 수 있습니다.
- return_type 1: 일요일=1, 월요일=2, 화요일=3, 수요일=4, 목요일=5, 금요일=6, 토요일=7
- return_type 2: 월요일=1, 화요일=2, 수요일=3, 목요일=4, 금요일=5, 토요일=6, 일요일=7
- return_type 3: 월요일=0, 화요일=1, 수요일=2, 목요일=3, 금요일=4, 토요일=5, 일요일=6
- return_type 11-17: 다양한 주 시작일 조합으로 월요일부터 일요일까지 선택 가능
실무에서 자주 사용되는 요일 계산 패턴들
프로젝트 관리에서는 주로 평일만 계산하거나 특정 요일의 빈도를 파악해야 합니다. 평일(월-금) 개수를 구하려면 월요일부터 금요일까지 각각 계산한 후 합계를 구하는 방법이 있습니다. 또는 NETWORKDAYS 함수를 사용하여 더 간단하게 해결할 수도 있습니다. WEEKDAY 함수의 다양한 활용 예시는 이 자료에서 더 자세히 확인할 수 있습니다.
| 요일 | WEEKDAY 값 | 실무 활용 예시 |
|---|---|---|
| 월요일 | 2 (기본) / 1 (type=2) | 주간 회의 일정 계산 |
| 화요일 | 3 (기본) / 2 (type=2) | 정기 업무 보고서 제출일 |
| 수요일 | 4 (기본) / 3 (type=2) | 중간 점검일 설정 |
| 목요일 | 5 (기본) / 4 (type=2) | 마케팅 캠페인 런칭일 |
ROW와 INDIRECT 함수의 협력 메커니즘
ROW(INDIRECT(시작날짜&”:”&종료날짜)) 구조는 엑셀의 독특한 특성을 활용한 기법입니다. INDIRECT 함수는 텍스트 형태의 참조를 실제 셀 참조로 변환하고, ROW 함수는 해당 행 번호들을 배열로 반환합니다. 예를 들어 2023년 1월 1일이 44927이라는 일련번호를 가지고 있다면, ROW(INDIRECT(“44927:44933”))는 {44927;44928;44929;44930;44931;44932;44933} 배열을 생성합니다. 이는 1주일간의 모든 날짜를 나타내며, 각 날짜에 WEEKDAY 함수를 적용하면 해당 주의 요일 패턴을 파악할 수 있습니다.
이러한 배열 처리 방식은 VBA를 사용하지 않고도 복잡한 날짜 계산을 가능하게 합니다. 특히 대량의 데이터를 처리할 때 반복문 없이도 효율적인 계산이 가능하며, 동적 배열 함수가 도입된 최신 엑셀에서는 더욱 강력한 성능을 보여줍니다. 마이크로소프트 공식 WEEKDAY 함수 문서에서 더 자세한 기술적 내용을 확인하실 수 있습니다.
SUMPRODUCT 함수로 조건부 계산 최적화하기
SUMPRODUCT 함수는 배열의 각 요소를 곱한 후 합계를 구하는 함수이지만, 조건부 계산에서 매우 유용합니다. (WEEKDAY(배열)=특정요일)*1 형태에서 조건이 맞으면 TRUE(1), 틀리면 FALSE(0)가 되어 해당 요일만 카운트됩니다. 이는 COUNTIFS 함수로는 구현하기 어려운 복잡한 날짜 조건을 처리할 수 있게 해줍니다. SUMPRODUCT는 다중 조건을 동시에 처리할 수 있어, 예를 들어 특정 기간의 평일이면서 동시에 특정 월에 해당하는 날짜만 계산하는 것도 가능합니다.
실제 업무에서는 =SUMPRODUCT((WEEKDAY(날짜범위,2)<=5)*1)와 같은 공식으로 평일만 계산하거나, =SUMPRODUCT((WEEKDAY(날짜범위,2)>=6)*1)로 주말만 계산할 수 있습니다. 이러한 유연성 덕분에 SUMPRODUCT는 날짜 계산뿐만 아니라 다양한 조건부 집계 작업의 핵심 함수로 활용되고 있습니다.
오류 해결과 실무 팁 모음
날짜 계산에서 자주 발생하는 문제들과 해결방법을 알아보겠습니다. 첫째, 날짜가 텍스트 형식으로 입력된 경우 DATEVALUE 함수로 변환해야 합니다. 둘째, 빈 셀이 포함된 범위에서는 예상과 다른 결과가 나올 수 있으므로 IFERROR나 IF(ISBLANK()) 함수로 처리해야 합니다. 셋째, 시작날짜가 종료날짜보다 큰 경우를 대비해 MIN, MAX 함수를 활용한 검증 로직을 추가하는 것이 좋습니다.
성능 최적화 측면에서는 큰 날짜 범위를 처리할 때 배열 공식의 계산 속도를 고려해야 합니다. 수만 개의 날짜를 처리하는 경우에는 헬퍼 열을 만들어 단계적으로 계산하는 것이 더 효율적일 수 있습니다. 또한 조건부 서식과 연계하여 특정 요일을 시각적으로 표시하면 데이터 검증이 더욱 쉬워집니다. WEEKDAY 함수의 고급 활용법과 성능 최적화 팁은 이 링크에서 더 자세히 확인할 수 있습니다.
고급 응용: 휴일 제외 계산과 커스터마이징
실제 업무에서는 단순히 요일만 계산하는 것이 아니라 공휴일이나 회사 휴무일을 제외해야 하는 경우가 많습니다. 이때는 NETWORKDAYS.INTL 함수와 조합하거나, 별도의 휴일 목록을 만들어 SUMPRODUCT 함수에 추가 조건으로 포함시킬 수 있습니다. 예를 들어 =SUMPRODUCT((WEEKDAY(날짜범위,2)=1)*(COUNTIF(휴일범위,날짜범위)=0))와 같은 공식으로 월요일이면서 동시에 휴일이 아닌 날짜만 계산할 수 있습니다.
또한 분기별, 월별 요일 분포를 분석하려면 YEAR, MONTH 함수와 조합하여 더 세밀한 조건을 설정할 수 있습니다. 이러한 고급 응용은 인사 관리, 프로젝트 계획, 매출 분석 등 다양한 분야에서 강력한 도구가 됩니다. 특히 Power Query나 피벗 테이블과 연계하면 대용량 데이터의 요일별 패턴 분석도 가능해집니다.
