[구글시트 꿀팁] 퇴근 후 잔업(OT) 시간, 특정 구간만 자동으로 계산하는 공식 완벽 정리 (앱시트 포함)

안녕하세요! 업무 생산성을 높여주는 엑셀/구글시트 팁을 공유합니다.

오늘은 실무에서 인사/총무 담당자분들이나 근태 관리 시스템을 만드시는 분들이 가장 골머리를 앓는 '특정 시간대와의 겹치는 시간 계산(Overtime Calculation)' 방법을 알아보겠습니다.

단순히 종료시간 - 시작시간을 하면 전체 근무 시간이 나오지만, **"우리는 오후 5시부터 8시까지만 OT로 인정해!"**라는 규정이 있다면 어떻게 해야 할까요? IF 함수를 여러 번 쓰다 보면 수식이 꼬이기 쉽습니다.

이럴 때 사용하는 가장 깔끔한 '교집합(Overlap)' 계산 공식을 소개합니다.

1. 문제 상황 정의

조건은 다음과 같습니다.

• OT 인정 구간: 17:00 ~ 20:00 (오후 5시 ~ 오후 8시)
• 시작시간(A열): 날짜와 시간이 포함된 형식 (YYYY-MM-DD HH:MM:SS)
• 종료시간(B열): 날짜와 시간이 포함된 형식
• 목표: 내가 일한 시간이 16:30 ~ 17:30이라면, OT 구간(17:00~20:00)과 겹치는 30분만 구해내야 함.

2. 구글시트 해결 공식 (복사해서 쓰세요)

데이터가 A2셀(시작시간), **B2셀(종료시간)**에 있다고 가정할 때, C2셀에 아래 수식을 입력하세요.

구글 시트에 아래 함수를 입력하세요.

 

=MAX(0, MIN(B2, INT(A2) + TIME(20,0,0)) - MAX(A2, INT(A2) + TIME(17,0,0)))

 

 

💡 수식 설명 (원리 이해하기)

이 수식은 **[나의 근무 구간]**과 **[회사의 OT 인정 구간]**의 교집합을 구하는 수학적 논리를 엑셀 함수로 옮긴 것입니다.

1. INT(A2) + TIME(17,0,0):
   • INT(A2)는 시간은 버리고 '날짜'만 가져옵니다.
   • 여기에 17시와 20시를 더해, 해당 날짜의 OT 시작/종료 기준시간(Timestamp)을 만듭니다.
2. MIN(종료시간, OT종료시간):
   • 내가 일을 일찍 끝냈으면 내 종료시간, 밤늦게까지 했으면 OT 인정 한계인 20시 중 더 빠른 시간을 선택합니다.
3. MAX(시작시간, OT시작시간):
   • 내가 17시 전에 일을 시작했으면 17시부터, 17시 넘어서 시작했으면 내 시작시간 중 더 늦은 시간을 선택합니다.
4. MAX(0, ...):
   • 위에서 구한 값들을 뺐을 때 음수(겹치는 시간이 없음)가 나오면 0으로 처리하여 계산 오류를 방지합니다.

 

3. (보너스) 앱시트(AppSheet) 적용 공식

이미지에 앱시트 질문도 있어서 보너스로 남겨드립니다. 앱시트는 엑셀과 문법이 약간 달라 LIST와 DATETIME 변환이 필요합니다.

AppSheet Virtual Column 수식:

Excel

 

TOTALHOURS(
  MAX(LIST(
    "000:00:00", 
    MIN(LIST([종료시간], DATETIME(DATE([시작시간]) & " 20:00:00"))) 
    - MAX(LIST([시작시간], DATETIME(DATE([시작시간]) & " 17:00:00")))
  ))
)

• 앱시트에서는 MAX(A, B) 대신 MAX(LIST(A, B)) 형태를 사용해야 합니다.
• 결과값 타입은 Duration으로 설정해주세요.

---

4. 마무리 및 서식 설정

구글시트에서 수식을 넣었는데 결과가 0.0416... 같은 소수점으로 나온다면? 당황하지 마세요!

1. 수식이 적힌 셀을 선택합니다.
2. 상단 메뉴 **[서식] > [숫자] > [지속 시간]**을 클릭합니다.
3. 또는 **[맞춤 숫자 형식]**에서 [h]:mm:ss를 입력하면 "1:30:00" 처럼 깔끔하게 표시됩니다.

이 공식 하나만 알아두면 야간수당, 점심시간 제외 계산 등 다양한 시간 계산 업무를 자동화할 수 있습니다.

도움이 되셨다면 공감 버튼 부탁드립니다! :)

 

또는 아래와 같이 C컬럼(C열)을 선택후 서식더보기를 눌러 24시간 형식으로 바꿔 주시면 됩니다.

 

---

도움 되셨길 바라고, 우리 모두 엑셀/구글시트로 업무 자동화에 성공해 봐요!!