엑셀로 담당자별 ‘작업완료 후 다음 약속까지 대기시간’을 계산하는 스마트 수식 정리
업무 일정이 빽빽한 조직에서는 “담당자별로 작업이 끝나고 다음 약속까지 얼마만큼의 여유시간이 있는지” 파악하는 것이 매우 중요합니다. 이 대기시간을 정확히 계산하면, 일정 겹침이나 대기비용 낭비를 줄이고, 인력 운영을 효율화할 수 있습니다. 이번 글에서는 같은 담당자(B열)와 같은 일자(C열) 를 기준으로, 직전 서비스완료시간(E열) 과 현재 약속시간(D열) 을 비교하여 자동으로 “대기(여유)시간”을 계산하는 방법을 정리합니다.
🔍 문제 상황
단순히 “이전 행의 완료시간”을 기준으로 빼는 수식(=D3-E2)은 정렬 순서가 바뀌거나 담당자가 다를 경우 잘못된 계산을 유발합니다. 예를 들어, A담당자가 10시에 끝내고 B담당자가 12시에 시작한다면, 이 둘은 전혀 연결되지 않지만 엑셀은 단순히 위아래 행을 연결해버립니다.
따라서, 담당자와 일자가 모두 같은 조건에서 ‘가장 최근 완료시간’을 찾아야 합니다.
🧮 방법 1. MAXIFS + 보조열 활용 (가장 간단하고 호환성 높음)
- 보조열 추가(G열)
각 행의 고유 행번호를 자동으로 기록합니다.
=ROW()
2. 대기시간 수식(F3)
아래 수식을 F3에 입력 후 아래로 복사합니다.
=IFERROR(
D3 - MAXIFS($E$3:$E$1000, $B$3:$B$1000, $B3, $C$3:$C$1000, $C3, $G$3:$G$1000, "<"&$G3),
""
)
-
- $B3 → 담당자
- $C3 → 일자
- $E3:$E$1000 → 완료시간 범위
- " < "&$G3 → 현재 행보다 앞선 행들만 검색
이렇게 하면 같은 담당자·같은 일자 내에서 직전 완료시각의 최댓값(MAXIFS) 을 찾고, 그 차이를 대기시간으로 계산합니다.
3. 표시 형식
대기시간이 시간 단위로 보이도록 F열을 선택하고 [셀 서식 → 사용자 지정 → [h]:mm] 으로 지정합니다.
24시간이 넘어도 정상 표시됩니다.
🧮 방법 2. LET + FILTER 동적배열 버전 (최신 Excel용)
동적배열을 지원하는 버전(Office 365 등)에서는 다음 수식을 사용하면 보조열 없이도 계산 가능합니다.
=LET(
p,$B3, dt,$C3, apt,$D3,
prevs, FILTER($E$3:$E$9999, ($B$3:$B$9999=p)*($C$3:$C$9999=dt)*(ROW($B$3:$B$9999)<ROW())),
IF(apt="","", IFERROR(apt - MAX(prevs), ""))
)
이 수식은 조건에 맞는 이전 행의 완료시간만 필터링한 뒤, 그중 가장 큰 값(직전 완료시간)을 찾아 약속시간과의 차이를 계산합니다.
필요에 따라 지연시간(음수)을 제외하려면 apt - MAX(prevs) 대신 MAX(0, apt - MAX(prevs))를 사용하면 됩니다.
📊 정렬 및 관리 팁
정확한 계산을 위해서는 담당자 → 일자 → 약속시간 순으로 오름차순 정렬이 되어 있어야 합니다.
정렬이 엉켜 있으면 실제 순서와 계산 순서가 달라져 엉뚱한 대기시간이 나옵니다.
추가로, 피벗테이블이나 Power Query를 활용하면 각 담당자별 평균 대기시간, 총 대기시간, 일별 패턴 등을 한눈에 분석할 수 있습니다. 예를 들어 “A담당자는 평균 25분, B담당자는 1시간 10분 대기”처럼 생산성 분석 지표로 활용 가능합니다.
💡 정리
| 항목 | 내용 |
| 비교기준 | 담당자(B), 일자(C) |
| 계산식 | 약속시간(D) – 직전 완료시간(E) |
| 핵심함수 | MAXIFS 또는 FILTER + MAX |
| 표시형식 | [h]:mm |
| 확장활용 | 담당자별 평균대기시간 피벗, 가동률 분석 |
✅ 결론
이 방법은 단순히 시간을 빼는 것이 아니라, 조건부로 ‘직전 완료시각’을 찾아내는 구조이기 때문에 업무현장, 상담예약, 공장공정 스케줄링 등 다양한 상황에서 활용할 수 있습니다.
특히 여러 명의 담당자와 복수 일정이 혼재된 환경에서 “대기시간”, “가동률”, “리드타임 간격”을 자동으로 계산하고자 할 때 매우 실용적입니다.
엑셀만으로도 이런 시간 기반 분석을 구현할 수 있다는 점에서, 이번 수식은 실무 생산성 향상에 큰 도움이 될 것입니다.