이 글의 목적은 엑셀에서 날짜·시간 간격 계산 시 음수 값이 발생할 때 표시 오류(####) 또는 0 처리 문제를 실무 수준에서 완벽히 해결하도록 공식, 서식, 옵션, 파워 쿼리, VBA까지 단계별로 정리하는 것이다.
1. 문제 정의와 원리: 엑셀의 날짜·시간 직교좌표 이해
엑셀은 날짜·시간을 일(day) 단위의 실수로 저장한다. 1은 24시간, 0.5는 12시간, 1/24는 1시간을 의미한다. 시간 차이는 보통 종료−시작으로 구한다. 그러나 기본 1900 날짜 시스템에서는 음수 시간과 음수 날짜가 시리얼 수로 존재하더라도 시간 형식으로 표시할 수 없어서 셀에 ####로 보이거나 0처럼 인식되는 현상이 발생한다. 이 제약이 본문에서 다루는 핵심 문제이다.
2. 가장 안전한 기본 해법: 숫자 결과와 텍스트 표기 분리
보고용 표에서는 사람이 읽을 수 있는 “부호 포함 형식”, 분석용 열에서는 “순수 숫자값”을 유지하면 모든 경우의 수를 안전하게 처리한다.
| 목표 | 공식 | 표시 형식 | 장점 | 주의 |
|---|---|---|---|---|
| 총 분(음수 포함) 숫자로 계산 | = (종료-시작) * 24 * 60 | 일반 | 피벗, 통계, 합계 등 모든 계산에 직결 가능 | 표시는 분 단위이므로 시:분 서식 표시 열을 별도로 둔다 |
| 사람이 읽는 부호 포함 시:분 | =IF(종료>=시작, TEXT(종료-시작,"[h]:mm"), "-"&TEXT(시작-종료,"[h]:mm")) | 텍스트 | #### 미발생, 마이너스 부호 유지 | 텍스트라서 합계·평균에 직접 사용 불가 |
| 자정 넘김(당일 기준 경과 시간) | =MOD(종료-시작,1) | [h]:mm 또는 [h]:mm:ss | 근무교대, 야간조, 장비가동에 적합 | 부호 개념이 없는 “순 양(+)” 경과시간 |
| 순수 일수 차이(날짜만) | =종료_날짜-시작_날짜 | 숫자 또는 사용자 지정 | 일 단위 SLA, 납기일 지연 계산 | 시각 값이 섞이면 소수 발생 |
| 연·월·일 구성 요소 분해 | =DATEDIF(시작,종료,"d") 등 | 숫자 | 휴가·근속·보증기간 산정 | DATEDIF는 음수 입력 시 오류 |
주의 : “[h]:mm:ss”와 같은 사용자 지정 서식은 24시간을 초과하는 합계를 올바르게 표시하지만, 기본 1900 시스템에서 음수 시간은 여전히 표시하지 못한다. 부호를 표시하려면 텍스트 공식 또는 1904 시스템 전환이 필요하다.
3. 자정 넘김(야간조) 시 대표 패턴
같은 날짜 셀에 시작=22:00, 종료=06:00처럼 종료가 다음 날인 경우가 많다. 이때 “경과 시간”이 목적이라면 다음과 같이 계산한다.
=MOD(종료-시작, 1) // 결과는 양의 시각 값 // 표시 형식: [h]:mm 또는 [h]:mm:ss 부호가 필요 없는 경과시간(예: 설비 가동시간, 근무시간)에는 MOD가 가장 안전하다.
4. 음수 시간을 가독성 있게 표시하는 텍스트 공식을 표준화
보고서에서 음수 부호 포함 시:분을 보여주려면 텍스트로 만드는 것이 실무적으로 가장 깔끔하다.
// 시:분 =IF(종료>=시작, TEXT(종료-시작, "[h]:mm"), "-" & TEXT(시작-종료, "[h]:mm"))
// 시:분:초
=IF(종료>=시작, TEXT(종료-시작, "[h]:mm:ss"), "-" & TEXT(시작-종료, "[h]:mm:ss"))
같은 로직을 “총 분” 또는 “총 초” 숫자열과 함께 두면 데이터 분석과 시각화를 동시에 만족한다.
5. 분석용 숫자열 표준: 총 시간(또는 분)로 환산
피벗, 조건부 서식, 임계치 경고 등 수치 기반 처리를 위해서는 실수형 결과가 필요하다.
// 총 시간(십진수, ± 가능) = (종료 - 시작) * 24
// 총 분(± 가능)
= (종료 - 시작) * 24 * 60
// 총 초(± 가능)
= (종료 - 시작) * 24 * 60 * 60
이 값들은 음수라도 숫자로 남아 피벗 합계, 평균, 표준편차 계산이 가능하다. 표시만 별도 텍스트 열로 처리한다.
6. 1904 날짜 시스템 전환의 장단점
엑셀에는 1900과 1904 두 가지 날짜 시스템이 있다. 1904 시스템을 사용하면 음수 시간 표시가 가능해진다. 그러나 모든 시트의 날짜가 기준일 1904-01-01로부터 계산되어 1900 시스템 대비 약 1,462일 차이가 발생한다.
- 경로: 파일 → 옵션 → 고급 → “이 통합 문서 계산 시” → “1904 날짜 시스템 사용” 체크한다.
- 효과: 기존 날짜가 일괄 이동하므로 외부 파일과 혼용 시 심각한 오차가 발생한다.
주의 : 1904 시스템 전환은 “새 통합 문서 템플릿” 또는 “독립 분석 전용 파일”에만 권장한다. 조직 표준이 1900이라면 전환을 피하고 텍스트 표시 공식을 사용한다.
7. 로케일·서식 섞임으로 인한 표시 오류 대응
다른 지역 설정에서 작성된 파일은 쉼표/세미콜론, 24시간/AM-PM 표기, 커스텀 서식 차이로 오작동할 수 있다.
- 시간 서식은
[h]:mm:ss로 통일한다. - AM/PM 혼용 데이터는
TIMEVALUE로 정규화한다. - 텍스트 날짜·시간은
DATEVALUE와TIMEVALUE로 분리 변환 후 더한다.
// 텍스트 "2025-10-25 18:20:00" → 시리얼 =DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,8)) 주의 : “텍스트 같은 숫자”는 계산 전
VALUE 또는 곱하기 1로 실수화한다. 데이터 가져오기 단계에서 정규화하면 후속 오류가 줄어든다.8. SLA·지연 시간 계산 패턴
납기 지연 시간처럼 “기준보다 빠르면 음수”가 필요한 시나리오에서는 기준 시각을 기준선으로 잡고 차이를 부호 있는 숫자로 저장한다.
// 기준시각(Target) 대비 실측(Actual) 차이, 분 단위 = (Actual - Target) * 24 * 60 // 음수 = 조기, 양수 = 지연
// 보고용 표기
=IF(Actual>=Target, TEXT(Actual-Target,"[h]:mm"), "-"&TEXT(Target-Actual,"[h]:mm"))
9. 자주 발생하는 함정과 해결 체크리스트
| 증상 | 원인 | 해결 |
|---|---|---|
| #### 표시 | 음수 시간 + 시간 서식 | 텍스트 표기 공식을 사용하거나 숫자열로 저장, 또는 1904 전환 |
| 자정 넘김에 음수 | 단순 종료−시작 | MOD(종료-시작,1) 사용 |
| 합계가 24시간에서 초기화 | 서식이 hh:mm:ss | [h]:mm:ss로 변경 |
| DATEDIF 오류 | 시작 > 종료 | 입력 순서 보정 또는 IF로 분기 |
| 피벗에서 합계 틀림 | 보고용 텍스트 열 사용 | 숫자열 열을 피벗 값으로 사용 |
| 외부 링크와 날짜 어긋남 | 1900/1904 혼용 | 파일 단위로 시스템 통일 |
10. 파워 쿼리로 음수 시간 안전 처리
데이터 가져오기 단계에서 음수 시간 처리를 끝내면 엑셀 본시트는 단순화된다.
// (Power Query) 텍스트 열 "Start","End" → DateTime 변환 후 = Duration.From([End] - [Start]) // 기간 형식, 음수 허용 = Duration.TotalMinutes([End] - [Start]) // 총 분(숫자) = if [End] >= [Start] then [End]-[Start] else -([Start]-[End]) // 부호 일관성 필요 시 “사용자 지정 열”로 포맷 텍스트를 추가하고, 원본 유지/증분 새로 고침을 적용한다.
11. 동적 배열과 LET/LAMBDA로 표준 함수 만들기
반복되는 공식을 함수화하면 워크북 유지보수가 쉬워진다.
// 명명 관리자에 LAMBDA 등록: SIGNED.DURATION(start, end, fmt) // fmt 예: "[h]:mm" 또는 "[h]:mm:ss" =LAMBDA(s, e, fmt, IF(e>=s, TEXT(e-s, fmt), "-" & TEXT(s-e, fmt)) )
// 사용
=SIGNED.DURATION(B2, C2, "[h]:mm")
// 숫자 결과 함수: SIGNED.MINUTES(start, end)
=LAMBDA(s, e, (e - s) * 24 * 60)
12. 조건부 서식으로 부호 색상·아이콘 표시
보고 가독성을 높이려면 숫자열 열에 조건부 서식을 적용한다.
- 규칙 종류: 셀 값 < 0 → 서식 빨강.
- 아이콘 집합: 삼각형 또는 화살표 → 기준값 0 분.
- 보고열(텍스트)은 색만 바꾸고 값은 건드리지 않는다.
13. 실무 시나리오별 레시피
13.1 근태: 출근·퇴근 시간 차이
// 경과시간(자정 넘김 포함) =MOD(퇴근-출근, 1) // [h]:mm:ss 서식 // 지각/조퇴 판단(기준=09:00) = (출근 - TIME(9,0,0)) * 24 * 60 // 음수=조기, 양수=지각 13.2 설비 가동/다운타임
// 다운타임 총 분 = (재가동 - 다운발생) * 24 * 60
// 가동률(%) = 가동시간 / 총가용시간
= MOD(가동종료-가동시작,1) / (작업종료-작업시작)
13.3 물류 SLA 조기/지연
// 조기 음수, 지연 양수(분) = (실제납품 - 약정납품) * 24 * 60
// 보고열
=IF(실제납품>=약정납품, TEXT(실제납품-약정납품,"[h]:mm"), "-"&TEXT(약정납품-실제납품,"[h]:mm"))
14. 데이터 정규화: 텍스트·혼합열 일괄 정리 스크립트
외부 CSV·시스템 로그에서 흔한 혼합 데이터는 다음 순서로 정규화한다.
- 열 복제 후 원본 보관한다.
- 공백·제어문자 제거:
=TRIM(CLEAN(A2))을 적용한다. - 날짜와 시간 분리:
TEXTSPLIT또는LEFT/MID/RIGHT조합을 사용한다. DATEVALUE+TIMEVALUE로 변환 후 합산한다.- 오류는
IFERROR로 0 또는 공백 처리한다.
// 예시: "25/10/2025 23:55:02" (일/월/년) =DATEVALUE(TEXT(A2,"dd/mm/yyyy")) + TIMEVALUE(TEXT(A2,"hh:mm:ss")) 15. VBA 사용자 정의 함수(UDF)로 일관 포맷 제공
조직 통일 함수가 필요하면 VBA UDF를 사용한다.
Function SignedDuration(ByVal StartTime As Double, ByVal EndTime As Double, _ Optional ByVal FormatText As String = "[h]:mm") As String Dim diff As Double diff = EndTime - StartTime If diff >= 0 Then SignedDuration = Format(diff, FormatText) Else SignedDuration = "-" & Format(-diff, FormatText) End If End Function 시트에서 =SignedDuration(B2,C2,"[h]:mm:ss")처럼 호출하면 음수 부호가 유지된 텍스트 결과를 얻는다. 숫자 연산은 별도 열에 =(C2-B2)*24*60을 사용한다.
16. 성능·정확도 관리 팁
- 대용량 시 계산은 “총 분(숫자)” 열을 기준으로 피벗·차트화한다.
- 보고는 텍스트 열을 참조한다. 다만 합계·평균은 숫자열을 사용한다.
[h]:mm:ss서식을 사용해 24시간 초과 합계를 보장한다.- 1900/1904 시스템 혼용은 금지한다. 외부에서 온 파일은 즉시 시스템을 확인한다.
- 파워 쿼리 단계에서
Duration으로 통일하면 후처리가 단순해진다.
17. 종합 예제: 템플릿 구성
// A열: 시작, B열: 종료 (DateTime) // C열: 총분(숫자) C2: = (B2 - A2) * 24 * 60
// D열: 보고용 시:분 텍스트
D2: = IF(B2>=A2, TEXT(B2-A2,"[h]:mm"), "-" & TEXT(A2-B2,"[h]:mm"))
// E열: 자정 넘김 경과시간
E2: = MOD(B2 - A2, 1) // 서식 [h]:mm:ss
// 조건부 서식: C열 < 0 → 빨강
FAQ
음수 시간을 그냥 숫자 형식으로 두면 왜 ####가 나오지 않나?
숫자 형식(일반, 숫자)로 두면 ####가 나오지 않는다. ####는 “시간/날짜 서식”으로 표시하려 할 때 음수 값을 렌더링하지 못해 발생한다. 해결은 텍스트 표기 또는 1904 시스템 전환, 혹은 숫자형 유지 후 보고 열 분리이다.
[h]:mm:ss 서식이면 100시간도 보이나?
보인다. 대괄호가 누적 시간의 총합을 의미한다. 다만 음수 시간은 표시하지 못한다.
DATEDIF로 음수 차이를 구할 수 있나?
불가하다. DATEDIF는 시작이 종료보다 커지면 오류를 낸다. 음수 차이는 단순 시리얼 차이(종료−시작)를 사용하고 단위를 곱해 환산한다.
자정 넘김과 음수 부호를 동시에 처리하려면?
경과시간 목적이면 MOD(종료-시작,1)을 쓴다. 부호가 필요한 SLA 목적이면 (종료-시작)*24*60처럼 숫자열을 만들고 보고열은 텍스트 공식으로 부호를 붙인다.
1904 시스템을 켜면 모든 문제가 해결되나?
표시는 쉬워지지만 날짜 기준이 달라져 다른 파일과 날짜가 어긋난다. 기존 자산과 혼용되면 치명적이다. 새 템플릿에만 선택적으로 사용한다.
파워 쿼리가 음수 시간에 더 적합한가?
그렇다. Duration 형식은 음수도 자연스럽게 처리한다. 집계용 숫자열(Duration.Total*)과 보고용 포맷을 동시에 생성하면 본시트 공식이 단순해진다.