엑셀로 담당자별 ‘작업완료 후 다음 약속까지 대기시간’을 계산하는 스마트 수식 정리

업무 일정이 빽빽한 조직에서는 “담당자별로 작업이 끝나고 다음 약속까지 얼마만큼의 여유시간이 있는지” 파악하는 것이 매우 중요합니다. 이 대기시간을 정확히 계산하면, 일정 겹침이나 대기비용 낭비를 줄이고, 인력 운영을 효율화할 수 있습니다. 이번 글에서는 같은 담당자(B열)와 같은 일자(C열) 를 기준으로, 직전 서비스완료시간(E열) 과 현재 약속시간(D열) 을 비교하여 자동으로 “대기(여유)시간”을 계산하는 방법을 정리합니다.

 

---

🔍 문제 상황

단순히 “이전 행의 완료시간”을 기준으로 빼는 수식(=D3-E2)은 정렬 순서가 바뀌거나 담당자가 다를 경우 잘못된 계산을 유발합니다. 예를 들어, A담당자가 10시에 끝내고 B담당자가 12시에 시작한다면, 이 둘은 전혀 연결되지 않지만 엑셀은 단순히 위아래 행을 연결해버립니다.
따라서, 담당자와 일자가 모두 같은 조건에서 ‘가장 최근 완료시간’을 찾아야 합니다.

---

🧮 방법 1. MAXIFS + 보조열 활용 (가장 간단하고 호환성 높음)

1. 보조열 추가(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),
              ""
           )

---

 

1. • $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
확장활용	담당자별 평균대기시간 피벗, 가동률 분석

---

✅ 결론

이 방법은 단순히 시간을 빼는 것이 아니라, 조건부로 ‘직전 완료시각’을 찾아내는 구조이기 때문에 업무현장, 상담예약, 공장공정 스케줄링 등 다양한 상황에서 활용할 수 있습니다.
특히 여러 명의 담당자와 복수 일정이 혼재된 환경에서 “대기시간”, “가동률”, “리드타임 간격”을 자동으로 계산하고자 할 때 매우 실용적입니다.

엑셀만으로도 이런 시간 기반 분석을 구현할 수 있다는 점에서, 이번 수식은 실무 생산성 향상에 큰 도움이 될 것입니다.