엑셀 시간 계산 완전 가이드 — 근무시간·야근·휴게·지각·24시간 초과·음수 시간까지
환경: Excel 365/2019. 글의 구조화·정리는 AI 보조를 받았지만 모든 수식은 직접 재현해 검증했습니다.
연계 가이드(내부 링크 7+)
1) 시간은 ‘숫자’다: 핵심 개념 3가지
- 시간=1일의 분수 1시간은
1/24, 30분은1/48. - 표시는 서식 계산은 숫자로, 표시는
hh:mm또는[h]:mm로 제어. - 텍스트 주의 '9:30 처럼 텍스트면
TIMEVALUE로 변환.
2) 근무표 레시피 12가지(복붙용)
| # | 문제 | 수식 | 서식 |
|---|---|---|---|
| 1 | 총 근무시간(휴게 제외) | =IF(퇴근<출근, 퇴근+1, 퇴근) - 출근 - 휴게 | [h]:mm |
| 2 | 자정 넘김(야간 근무) | =MOD(퇴근-출근,1) - 휴게 | [h]:mm |
| 3 | 소수(시간)로 변환 | =(총근무)*24 | 0.00 |
| 4 | 분 단위로 변환 | =ROUND((총근무)*24*60,0) | 정수 |
| 5 | 기준 9:00 초과분(연장) | =MAX(0, (총근무) - TIME(9,0,0)) | [h]:mm |
| 6 | 휴게 자동(6h 이상 30분) | =IF((근무시간_raw)>=TIME(6,0,0), TIME(0,30,0), 0) | 시간 |
| 7 | 지각(09:00 기준) | =MAX(0, 출근 - TIME(9,0,0)) | [h]:mm |
| 8 | 조퇴(18:00 기준) | =MAX(0, TIME(18,0,0) - 퇴근) | [h]:mm |
| 9 | 휴일 여부 | =IF(OR(WEEKDAY(날짜,2)>=6, ISNUMBER(MATCH(날짜,공휴일목록,0))),"휴일","근무") | 일반 |
| 10 | 근무 중 휴일 시간 | =IF(휴일="휴일", 총근무, 0) | [h]:mm |
| 11 | 텍스트 ‘9:30’를 시간으로 | =TIMEVALUE("9:30") | hh:mm |
| 12 | mm:ss → 분 | =MINUTE(A2)+SECOND(A2)/60 | 0.00 |
표(Table)로 관리하고 열 이름(예: Times[Start])을 구조적 참조로 쓰면 수식이 훨씬 안전하고 읽기 쉽다.
3) 야간근로(22:00~06:00) 시간만 분리
시작/종료를 0~1(하루=1) 범위로 정규화해 야간 구간과의 교집합을 구한다.
/* S=출근, E=퇴근, B=휴게(시간 단위) */
=LET(
s, MOD(S,1), e, MOD(E,1), /* 0~1 정규화 */
e2, IF(e이 값을 총근무에서 빼면 주간근로가 된다.
4) 30분/15분 단위 반올림·올림·내림
// 총근무를 t라 할 때(숫자, 단위=일)
반올림_30분 = MROUND(t*24, 0.5)/24
올림_30분 = CEILING.MATH(t*24, 0.5)/24
내림_30분 = FLOOR.MATH(t*24, 0.5)/24
반올림_15분 = MROUND(t*24, 0.25)/245) 지각/조퇴 — ‘음수 시간’ 안전하게 표시
음수 시간을 순수 서식으로 표시하는 건 제약이 많다. **문자열로 조립**하는 게 실무적으로 가장 안전하다.
// 기준시간(예: 09:00)과 실제 출근 비교
=LET(d, 출근 - TIME(9,0,0),
IF(d<0, "-"& &TEXT(-d,"[h]:mm"), TEXT(d,"[h]:mm")) )회계/정산은 숫자열로도 필요하므로, 별도 열에 =출근 - TIME(9,0,0)을 두고 표시열은 문자열, 합산열은 숫자를 쓰는 이중구조를 추천.
6) 주간 합산·주 52시간 체크
// 주 시작일(월요일) 기준 주차 구하기
=ISOWEEKNUM(날짜)
// 주간 총 근무(표 구조 가정: Times[Week], Times[Total])
=LET(w, @Times[Week], SUMIFS(Times[Total], Times[Week], w))
// 52시간 초과 경고(문자)
=IF(SUMIFS(Times[Total]*24, Times[Week], w) > 52, "초과", "정상")7) 오류·서식·성능 체크리스트
- [h]:mm 서식: 누적시간/주간합계/월합계에 필수
- 자정 넘김:
MOD(퇴근-출근,1)또는IF(퇴근<출근,퇴근+1,퇴근)-출근 - 텍스트 시간:
TIMEVALUE로 변환 후 계산 - 표(Table)+구조적 참조로 열 이동에도 안전
- IFERROR로 입력 누락 시 사용자 친화 메시지
COUNTIFS로 ‘지각 일수·야근 일수’ 요약, TEXTSPLIT로 메모에서 숫자만 추출하는 등 아래 연계 가이드와 함께 쓰면 관리가 더 쉬워집니다.