엑셀에서 시간 계산 오류(음수 시간 표시 등) 해결

이 글은 엑셀에서 음수 시간 표시, 24시간 이상 누적 시간 왜곡, 날짜·시간 빼기 결과 오류 등 시간 계산과 관련된 대표적인 문제의 원인과 해결책을 정리하여, 실무자가 정확한 근무 시간·공정 시간·프로젝트 경과 시간을 처리하도록 돕기 위함이다.

엑셀 시간 계산의 기본 원리

엑셀은 날짜·시간을 일련번호(serial number)로 저장한다. 1은 1900-01-01 00:00이고, 0.5는 1900-01-00 12:00이다. 1일보다 작은 분수는 하루 24시간을 1로 보는 10진수 비율이다. 예를 들어 6시간은 0.25이다. 셀 서식에 [hh]:mm 또는 hh:mm과 같은 시간 형식을 적용하여 분수값을 시·분으로 표시한다.

주요 시간 계산 오류와 증상

오류 유형	전형적 증상	주 원인
음수 시간 표시 불가	계산 결과가 #####로 보인다.	1900 날짜 시스템은 음수 일련번호를 표시하지 못한다.
24시간 이상 누적 오류	25:30이 1:30으로 바뀐다.	표준 hh:mm 서식은 24시간을 넘으면 하루를 넘긴 몫을 삭제한다.
날짜 덧셈/뺄셈 결과 왜곡	야간 근무(22:00~05:00) 합계가 17:00로 나온다.	종료 시간이 자정을 넘기면 음수 계산이 발생한다.
텍스트 형식 시간	'08:30'이 계산에 사용되지 않는다.	앞에 작은따옴표 또는 공백이 붙어 순수 숫자·시간이 아니다.

오류별 해결 방안

오류 유형	점검 절차	해결 방법
음수 시간	파일 → 옵션 → 고급 → 1904 날짜 시스템 사용 체크 여부 확인 음수 결과 식별: =IF(B2-A2<0,TRUE,FALSE)	통합 문서 전체를 1904 시스템으로 전환하다. 또는 =TEXT(ABS(B2-A2),"[hh]:mm")&"-"처럼 문자 변환하여 값·표시 분리하다.
24시간 이상 누적	합계 셀 서식이 hh:mm인지 확인한다.	[hh]:mm 또는 [h]:mm:ss 서식으로 변경하여 24시간을 넘어도 누적 표시하다.
자정(밤샘) 계산	종료시간 < 시작시간 여부 검사	=MOD(종료시간-시작시간,1) 을 사용하여 음수 없이 24시간 모듈로 차이를 구하다.
텍스트 시간	ISTEXT() 함수로 확인하다.	=TIMEVALUE()나 --텍스트 방식으로 숫자로 변환하거나 데이터 → 텍스트 나누기를 실행하다.

실습 예제 1 : 음수 근무 시간 오류

1단계 : 오류 재현

근무 시작 | 근무 종료 | 근무 시간
---------------------------------
22:30     | 05:30     | =B2-A2

결과 셀이 #####로 표시된다.

2단계 : 해결

=IF(B2 < A2, B2+1, B2) - A2

또는 모듈러 방식을 써서

=MOD(B2-A2,1)

를 입력한다. 끝으로 셀 서식을 [hh]:mm로 설정하면 07:00이 올바르게 표시된다.

실습 예제 2 : 장시간 프로젝트 경과 누적

시작 일시       | 종료 일시       | 경과 시간
---------------------------------------------
2024-11-01 11:00 | 2024-11-03 18:30 | =(B2-A2)

기본 서식이면 55:30 대신 07:30만 표시된다. 합계 셀·결과 셀 모두 서식을 [hh]:mm로 바꾸면 55:30이 표시된다.

VBA 매크로 : 음수 시간 자동 정정

'#########################################################
' 선택 범위에서 음수 시간을 찾아 24시간을 더해 양수로 환산 후
' [hh]:mm 서식을 적용한다.
'#########################################################
Sub FixNegativeTime()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Selection
        If IsDate(cell.Value) Then
            If cell.Value < 0 Then
                cell.Value = cell.Value + 1          '24시간 더하기
            End If
            cell.NumberFormat = "[hh]:mm"
        End If
    Next cell
    Application.ScreenUpdating = True
    MsgBox "음수 시간 보정이 완료되었다.", vbInformation
End Sub

Power Query로 시간 오류 방지

1. 데이터 → 가져오기로 원본 로드하다.
2. 쿼리 편집기에서 열 추가 → 사용자 지정 열에 Duration.TotalHours([End]-[Start]) 사용하다.
3. 음수가 나오면 if [Custom]<0 then [Custom]+24 else [Custom]로 처리하다.
4. 마지막으로 시간(기간) 형식으로 변환하여 로드한다.

팁: 시간 데이터가 자주 엑셀↔CSV↔DB로 이동한다면, 원본 필드를 초 단위 정수(Unix Time, 일련 초)로 저장하고 보고서 단계에서만 시간 서식을 적용하면 음수·누적 문제를 원천 차단할 수 있다.

시간 계산 Best Practice

• [hh]:mm 서식 습관화 : 작업시간·누적시간 열은 입력 직후 서식을 [hh]:mm로 설정하다.
• 24시간 모듈 공식 : 자정 교차 가능성이 있는 차이는 MOD(종료-시작,1)로 계산하다.
• 날짜+시간 분리 저장 : DB·CSV 인터페이스에서 날짜(정수)와 시간(실수)을 별 컬럼에 저장하면 가독성과 정확성이 올라간다.
• 1904 시스템 일관성 관리 : 외부 파일에서 1904 시스템을 쓰면 병합 전에 플래그를 기록하여 혼선 방지하다.
• 사용자 정의 함수(UDF) : 조직 공용 추가 기능으로 =TimeDiff(Start,End,24) 같은 UDF를 배포해 사용하다.

FAQ

Q1. 1904 시스템 사용 시 날짜가 4년 이상 달라진다.

A1. 1904 시스템은 기준일이 1904-01-01이므로, 일반(1900) 시스템과 1,462일(4년)의 오차가 생긴다. 통합 문서 간 복사·붙여넣기 전 반드시 시스템을 맞추거나 =A2+1462 식으로 기준을 변환한다.

Q2. [hh]:mm 서식을 적용했는데 100시간이 4일 4:00으로 바뀐다.

A2. 날짜와 시간 서식이 동시에 적용된 셀일 수 있다. 셀 서식 → 사용자 지정에서 [hh]:mm만 남기고 날짜 코드(yyyy, mm, dd)는 모두 삭제한다.

Q3. 음수 시간을 텍스트로라도 표시하려면?

A3. =TEXT(ABS(B2-A2),"[hh]:mm")&"−" 처럼 결과 뒤에 음수 기호를 덧붙여 문자열로 처리한다. 이후 계산 시에는 값으로 변환해야 한다.

Q4. 시간 합계가 소수점(2.75)으로 표시된다.

A4. 해당 셀 서식이 일반 또는 숫자 형식이다. 시간을 시·분으로 보이게 하려면 [hh]:mm 서식으로 바꾼다. 2.75 는 66:00(2일 18시간)과 동일하다.

Q5. 구글 스프레드시트 호환 시 음수 시간 해결법은?

A5. 스프레드시트는 음수 시간을 표시할 수 있으므로, 필요 시 1900 시스템 그대로 업로드 후 구글에서 서식 → 숫자 → 시간(24시간)을 적용하면 음수도 표시된다.