이 글의 목적은 엑셀에서 시간 계산 시 발생하는 음수 시간 표시 불가, #### 오류, #VALUE! 오류, 날짜·시간 혼합 데이터 문제를 체계적으로 진단하고 실무에서 바로 적용 가능한 해결책을 제시하는 것이다.
1. 엑셀 시간 계산이 꼬이는 근본 원리 이해
엑셀은 날짜·시간을 일 단위 연속 실수로 저장한다. 정수부는 날짜, 소수부는 하루 중 경과 비율이다. 예를 들어 0.5는 12:00:00을 의미한다. 1은 하루(24시간)이다. 두 시간의 차이는 단순히 실수의 차이이며, 서식을 시간으로 지정하면 사람이 읽을 수 있는 표기로 보이게 된다.
엑셀 기본 날짜 시스템(Windows 기본)은 1900 날짜 시스템이다. 이 시스템에서 음수 시간은 직접 표시하지 못한다. 즉 계산 결과가 0보다 작으면 셀에는 #### 또는 1900-01-00 형태의 비정상 표시가 나타난다. 이는 표시 문제이며 내부 계산 자체는 유효하다.
주의 : 음수 시간을 그대로 표시하려면 1904 날짜 시스템을 사용해야 한다. 그러나 통합 문서 전체 날짜가 1,462일 이동하는副작용이 있으므로 기존 데이터와 연동된 파일에서는 신중히 결정해야 한다.
2. 대표 증상과 원인 매핑
| 증상 | 주요 원인 | 즉시 조치 |
|---|---|---|
| 음수 시간 대신 #### 표시 | 1900 날짜 시스템에서 음수 시간 표시 불가 | [방법 A] 1904 날짜 시스템 전환 또는 [방법 B] 수식·서식으로 우회 처리 |
| 야간 근무 종료-시작이 음수 | 종료 시각이 익일인데 날짜가 입력되지 않음 | 종료+1 로 보정하거나 MOD 함수로 24시간 순환 보정 |
| #VALUE! 오류 | 시간이 텍스트로 저장, 공백·문자 혼입, 빈 셀 연산 | TIMEVALUE, VALUE, SUBSTITUTE, TRIM으로 정규화 후 IFERROR로 래핑 |
| 합계가 24시간을 넘으면 00:00으로 리셋 | 표시 서식이 hh:mm 이라 총시간이 24로 나머지 표시됨 | 사용자 지정 서식을 [h]:mm 또는 [h]:mm:ss 로 변경 |
| 1904 전환 후 날짜가 밀림 | 시스템 변경으로 기준점 자체가 이동 | 전환 전 스냅샷 보관, 변환 보정식 적용 또는 시트/통합문서 단위 분리 |
3. 해결책 로드맵(의사결정 트리)
- 합계가 24시간 이상인가 → 예라면 표시 서식을 [h]:mm 또는 [h]:mm:ss로 변경한다.
- 음수 시간 표시가 필요한가 → 예라면 1904 날짜 시스템 전환을 검토한다. 전환이 위험하면 수식 기반 텍스트 표시로 우회한다.
- 야간 교대처럼 익일 종료인가 → 종료<시작이면 종료+1 또는 MOD를 사용하여 보정한다.
- #VALUE!가 발생하는가 → 원본 시간을 숫자로 정규화하고 IFERROR로 보호한다.
- 데이터가 대량/주기적으로 유입되는가 → Power Query로 정규화 파이프라인을 구성한다.
4. 방법 A: 1904 날짜 시스템으로 음수 시간 표시
전환 경로는 파일 > 옵션 > 고급 > 이 통합 문서 계산 시 > 1904 날짜 시스템 사용이다. 전환하면 음수 시간 표시가 가능해진다. 다만 모든 날짜가 +1,462일 이동하여 기존 일정·마감일과 불일치가 생길 수 있다.
주의 : 외부 참조 통합 문서나 기존 마감일이 있는 파일은 전환하지 않는 것이 안전하다. 전환이 필요하다면 다음 절차를 따른다.
- 원본 파일 백업본을 만든다.
- 날짜가 들어간 열을 복제하여 보정 열을 만든다.
- 전환 전후 날짜 차이를 계산하여 보정한다.
/* 1900 → 1904 전환 시 날짜 보정 예시 */ 보정날짜 = 원본날짜 - DATE(1904,1,1) + DATE(1900,1,1) + 1462 실무에서는 통합 문서 전체 전환 대신 시트 분리 또는 우회 수식을 우선 고려하는 것이 합리적이다.
5. 방법 B: 수식으로 음수 시간 안전하게 처리
5.1 야간 근무·跨일 계산
시작(B2) 22:00, 종료(C2) 06:00 형태에서 종료가 익일임을 반영한다.
=IF(C2 < B2, C2 + 1, C2) - B2 표시 서식을 [h]:mm 또는 [h]:mm:ss로 지정하면 총시간이 24를 넘어도 누적 표시가 가능하다.
팁 : 같은 로직을 MOD로 간결하게 쓸 수 있다. MOD는 24시간을 1로 취급하여 자동 순환한다.
=MOD(C2 - B2, 1) 5.2 음수 지속시간을 텍스트로 표기(1900 시스템 유지)
계산 결과가 음수일 때도 사람이 읽을 수 있는 문자열로 표현한다. 셀 서식 대신 TEXT 함수를 사용한다.
=LET( d, C2 - B2, sign, IF(d < 0, "-", ""), txt, TEXT(ABS(d), "[h]:mm:ss"), sign & txt ) 위 수식은 1900 시스템에서도 "-02:30:00"과 같이 안전하게 표기한다. 합계가 필요하면 별도 숨김 열에 실제 수치(MOD 또는 보정식)를 저장하고 보고용 열은 텍스트를 사용한다.
5.3 빈 셀·텍스트 입력에 대한 방어
외부에서 복사한 데이터는 시간처럼 보이는 텍스트가 섞여 있다. 다음 수식으로 정규화한다.
=IFERROR( TIMEVALUE(SUBSTITUTE(TRIM(A2)," ","")), IFERROR(VALUE(A2), "") ) 정규화 후 표시 서식을 h:mm 또는 h:mm:ss로 지정한다. 빈 문자열("")을 반환하여 합계 범위에 포함되지 않게 만든다.
6. 합계·평균·초 변환과 표시 서식
6.1 24시간 초과 합계
=SUM(D2:D100) // 표시 서식: [h]:mm:ss [h]:mm 또는 [h]:mm:ss 서식은 누적 시간을 24를 기준으로 리셋하지 않고 그대로 보여준다.
6.2 평균 근무시간
=AVERAGE(D2:D100) // 표시 서식: h:mm:ss 6.3 초·분·시간 단위 변환
/* 시/분/초 → 일수(엑셀 내부) */ =TIME(hours, minutes, seconds)
/* 일수 → 초 */
=일수 * 24 * 60 * 60
/* 초 → 표시용 h:mm:ss */
=TEXT(초/86400, "[h]:mm:ss")
7. 실무 패턴별 레시피
7.1 출근·퇴근 시각으로 근무시간 계산
/* 시작(B2), 종료(C2), 휴게(D2, 시간 단위) */ =MOD(C2 - B2, 1) - D2/24 // 표시 서식: [h]:mm 7.2 야간 수당 시간만 분리
야간 기준 22:00~06:00 구간을 분리 계산한다.
=LET( s, B2, e, IF(C2 < B2, C2 + 1, C2), n1, TIME(22,0,0), n2, TIME(6,0,0)+1, /* 근무 구간과 야간 구간의 교집합 */ overlap, MAX(0, MIN(e, n2) - MAX(s, n1)), TEXT(overlap, "[h]:mm") ) 7.3 음수 지연시간을 부호 포함 텍스트로 보고
=LET(d, 실제도착 - 기준도착, TEXT(ABS(d), "[h]:mm"), IF(d<0, "-"&TEXT(ABS(d),"[h]:mm"), TEXT(d,"[h]:mm"))) 7.4 CSV/시스템 로그의 타임스탬프 처리
타임스탬프가 "2025-10-25 23:59:59" 텍스트라면 다음을 사용한다.
=IFERROR(VALUE(SUBSTITUTE(A2,"-","/")), DATEVALUE(LEFT(A2,10)) + TIMEVALUE(RIGHT(A2,8))) 이후 표시 서식을 yyyy-mm-dd hh:mm:ss로 지정한다.
8. 서식 설정 체크리스트
- 총시간 합계가 필요한 열은 반드시 사용자 지정 서식을 [h]:mm 또는 [h]:mm:ss로 둔다.
- 보고용 음수 시간 표시는 TEXT 기반으로 만들고 실제 계산용 열은 숫자값을 유지한다.
- 시간 입력 열에는 데이터 유효성 검사로 00:00~23:59 범위를 강제한다.
- 조건부 서식을 사용하여 음수 결과(텍스트의 접두부 '-')를 강조한다.
/* 조건부 서식 수식 예 */ =LEFT($E2,1)="-" 9. 1904 날짜 시스템 전환의 안전 운영
전환은 파일 단위 설정이다. 혼용하면 외부 참조가 어긋나고 공유 시 혼란이 발생한다. 안전 운영 원칙은 다음과 같다.
- 전환 파일은 별도 버전명으로 저장한다.
- 모든 날짜 열에 "원본날짜"와 "표시날짜" 두 열을 둔다.
- 외부 연결이 있다면 연결 해제 또는 값 붙여넣기 후 전환한다.
- 전환 전·후 체크표로 대표 날짜를 샘플링하여 검증한다.
| 점검 항목 | 전환 전 | 전환 후 | 합격 기준 |
|---|---|---|---|
| 기준 샘플 10건 날짜 일치 | 값 기록 | 값 기록 | 보정 후 ±0일 |
| 합계 시간 값 | 값 기록 | 값 기록 | 동일 |
| 피벗테이블 필터 | 동작 확인 | 동작 확인 | 필터 정상 |
10. 텍스트 시간 대량 정규화(파워 쿼리 권장)
주기적 수집 데이터는 Power Query로 정규화하면 재현성과 속도가 향상된다. 다음은 표준 규칙이다.
- 원본 열 분리: 날짜, 시간, 타임존 등 구성 요소로 분리한다.
- 공백·문자 제거: Trim, Clean, Replace Values로 불필요 문자를 삭제한다.
- 형식 변환: Date/Time으로 형식을 강제한다.
- 지속시간 계산: DateTime.Subtract로 차이를 구하고 Duration 형식으로 캐스팅한다.
- 출력: 합계 열은 [h]:mm 표시를 유지하도록 셀 서식을 설정한다.
// M 언어 예시 스니펫 = Table.TransformColumns( #"Trimmed", { {"시간", each Time.From(_), type time} } ) 11. 자주 쓰는 사용자 지정 표시 서식 모음
| 서식 | 용도 | 예시 표시 |
|---|---|---|
| [h]:mm | 24시간 초과 누적 | 41:30 |
| [h]:mm:ss | 초 포함 누적 | 125:07:12 |
| hh:mm | 시계 시각 | 08:05 |
| yyyy-mm-dd hh:mm:ss | 타임스탬프 | 2025-10-25 14:33:21 |
| m"분 "s"초" | 러닝타임 가독화 | 05분 32초 |
12. 오류 방지 패턴과 테스트
- 입력 단계에서 날짜와 시간을 분리 기록하고 필요 시 합쳐서 계산한다.
- 합계·평균 범위는 숫자만 포함되도록 데이터 유효성 검사와 ISNUMBER 필터를 적용한다.
- 보고 열과 계산 열을 분리하여 음수 시간은 텍스트, 합계는 숫자 값을 유지한다.
- 샘플 테스트 행을 만들어 종료<시작, 시작·종료 공란, 텍스트 입력 등 에지 케이스를 검증한다.
/* 숫자 여부 검사 */ =IF(ISNUMBER(A2), A2, "") 13. 실전 예제: 교대근무 근무표 템플릿
아래 예제는 야간 교대 포함 근무시간, 야간수당시간, 지각·조퇴를 동시에 계산한다.
/* 가정: A열=날짜, B열=시작, C열=종료, D열=휴게(분), E열=기준시작, F열=기준종료 */
근무시간(H) = MOD(C2 - B2, 1) - D2/1440
야간시간(I) = LET(
s,B2, e,IF(C20, d, 0))
조퇴(K) = LET(d, F2 - C2, IF(d>0, d, 0))
/* 표시 서식 */
H:I → [h]:mm
J:K → 텍스트 표시: =IF(cell=0,"",TEXT(cell,"[h]:mm"))
14. 검증 체크리스트
- 대표 케이스 5종: 정상, 익일 종료, 음수 지연, 빈 값, 텍스트 시간에 대해 예상값과 일치하는가.
- [h]:mm 서식이 필요한 열 모두에 적용되었는가.
- 합계 셀은 숫자 서식이며 TEXT 결과를 더하지 않는가.
- 외부 파일과 연결 시 날짜 시스템이 일치하는가.
FAQ
왜 [h]:mm 서식을 써야 하는가
[h]:mm 서식은 총시간이 24를 넘어도 누적값을 그대로 표시하기 때문이다. 일반 hh:mm 서식은 24로 나누어 나머지만 보여준다.
1904 날짜 시스템을 켜면 무엇이 달라지는가
음수 시간 표시가 가능해지지만 모든 날짜 기준점이 이동한다. 기존 일정·외부참조가 있으면 불일치가 생긴다. 별도 파일로 운영하는 것이 안전하다.
음수 시간을 합계에 반영하려면 어떻게 하나
합계를 숫자로 유지하려면 MOD 기반 보정값을 합산하고, 보고용으로는 TEXT로 부호를 붙여 표시한다. 즉 계산 열과 표시 열을 분리한다.
#VALUE! 오류가 계속 뜬다
시간이 텍스트일 가능성이 높다. TIMEVALUE, VALUE, SUBSTITUTE, TRIM으로 정규화하고 IFERROR로 감싼다. 빈 셀은 ""로 처리하여 범위 합산에서 제외한다.
초·밀리초까지 관리하려면
초는 [h]:mm:ss, 밀리초는 사용자 지정 서식 [h]:mm:ss.000을 사용한다. 밀리초는 반올림 오차가 있을 수 있다.