이 글의 목적은 엑셀에서 텍스트로 저장된 시간 데이터를 표준 시간 형식(숫자 형식)으로 정확히 변환하고, 변환 과정에서 발생하는 대표 오류를 원인별로 진단하여 실무에서 즉시 적용 가능한 해결책을 제공하는 것이다.
왜 텍스트 시간 변환이 필요한가
시간은 엑셀 내부에서 1일을 1.0으로 보는 일수의 분수로 저장된다. 예를 들어 오전 6시는 0.25, 정오 12시는 0.5로 표현된다. 텍스트로 저장된 "12:30"이나 "12시 30분"은 숫자 계산과 시간 함수에 사용할 수 없으므로 표준 시간 형식으로 변환해야 한다. 변환 실패 시 #VALUE! 오류, 잘못된 합계, 정렬 오류가 발생한다.
빠른 체크리스트: 증상별 즉시 처방
| 증상 | 원인 | 해결책(요약) |
|---|---|---|
| "12:30"이 왼쪽 정렬 | 텍스트 저장 | =TIMEVALUE(A2)로 변환 후 표시 형식 적용 |
| "12시 30분" 한글 표기 | 구분자 불인식 | =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"시",":"),"분","")) |
| "930"처럼 네 자리 숫자 | 시분 결합 텍스트 | =TIME(INT(A2/100),MOD(A2,100),0) |
| "23:60" 또는 "24:00" | 분 또는 시 경계 초과 | 정규화 수식 사용 또는 데이터 정제 |
| 합계가 24시간에서 초기화 | 표시 형식 문제 | 사용자 지정 [h]:mm:ss로 변경 |
| AM/PM 혼용 데이터 | 서식 혼재 | =TIMEVALUE(TEXT(A2,"@")) 또는 구분자 통일 |
| 공백·보이지 않는 문자 | 유니코드 공백·개행 | =TIMEVALUE(SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160)," ")) |
| 한글 "오전 9:30" | 로캘 텍스트 | =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"오전 ",""),"오후 ",""))+IF(LEFT(A2,2)="오후",0.5,0) |
| CSV 가져오기 후 전부 텍스트 | 가져오기 옵션 | 데이터 탭 → 텍스트 나누기 → 열 데이터 형식 "날짜: MDY" 또는 "일반" 지정 |
| "12.30" 점 구분 | 로캘 차이 | =TIME(VALUE(TEXTBEFORE(A2,".")),VALUE(TEXTAFTER(A2,".")),0) |
주의 : 변환 수식의 결과는 숫자이다. 표시 형식을 "시:분:초"로 지정하지 않으면 일반 숫자처럼 보일 수 있다. 변환 후 반드시 셀 서식을 "시간" 또는 사용자 지정
hh:mm:ss·[h]:mm:ss로 지정한다.원리 이해: 엑셀의 시간 직교 좌표
엑셀은 1900 또는 1904를 기준으로 날짜를 정수, 시간을 1일의 분수로 관리한다. 합계가 1.25이면 1일 6시간이다. 따라서 텍스트를 숫자로 바꾸는 핵심은 "시·분·초를 24·1440·86400으로 나눈 합"이다.
=시/24 + 분/1440 + 초/86400 이 원리를 그대로 구현하면 어떤 형태의 텍스트도 안전하게 변환할 수 있다.
방법 1: TIMEVALUE 함수로 표준 "HH:MM[:SS]" 텍스트 변환
형식이 "08:05", "18:30:15"라면 다음이 가장 안정적이다.
=TIMEVALUE(A2) 배열로 처리할 경우:
=MAP(A2:A100,LAMBDA(x, TIMEVALUE(x))) 또는 구버전에서 채우기:
=TIMEVALUE(A2) 아래로 복사 후 값 붙여넣기로 고정한다.
주의 :
*1, --A2 같은 강제 숫자화는 콜론이 포함된 시간 텍스트에는 동작하지 않는 경우가 많다. 이때는 TIMEVALUE를 사용한다.방법 2: 한글 "시·분·초" 표기 정규화
"9시 30분 5초", "오후 1시 05분"처럼 로캘 텍스트는 구분자를 콜론으로 통일한 뒤 TIMEVALUE를 적용한다.
=LET(t,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"시",":"),"분",":"),"초",""), t2,SUBSTITUTE(SUBSTITUTE(t,"오전 ",""),"오후 ",""), tv,TIMEVALUE(t2), IF(LEFT(A2,2)="오후", tv + 0.5, tv)) 초가 생략된 경우에도 동작하도록 SUBSTITUTE 체인을 사용한다.
방법 3: 네 자리 숫자 "930" → 09:30, "1545" → 15:45
시분 결합 숫자형 텍스트·숫자는 다음과 같이 분리한다.
=TIME(INT(VALUE(A2)/100), MOD(VALUE(A2),100), 0) 초가 포함된 "93015"(즉 09:30:15) 처리:
=LET(n,VALUE(A2), h,INT(n/10000), m,INT(MOD(n,10000)/100), s,MOD(n,100), TIME(h,m,s)) 주의 : "75"처럼 두 자리만 있는 데이터는 해석 규칙을 사전에 정해야 한다. 예: "075"는 01:15인지 00:75인지 모호하다. 표준화 규칙을 문서화한다.
방법 4: 구분자가 점(.) 또는 하이픈(-)인 데이터
일부 시스템은 시간 구분에 점을 사용한다. 예: "12.30". 이 경우 분리 후 TIME로 재조합한다.
=TIME(VALUE(TEXTBEFORE(A2,".")), VALUE(TEXTAFTER(A2,".")), 0) 구버전 호환:
=TIME(VALUE(LEFT(A2,FIND(".",A2)-1)), VALUE(MID(A2,FIND(".",A2)+1,99)), 0) 방법 5: 불필요 공백·보이지 않는 문자 제거
웹·ERP 복사본에는 CHAR(160)(줄바꿈 없는 공백), 제어문자, 전각 콜론 등 비표준 문자가 포함될 수 있다. 먼저 정제한 뒤 변환한다.
=LET(t, SUBSTITUTe(TRIM(CLEAN(A2)),CHAR(160)," "), t2, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,"：",":"),"，",":")," ", ""), TIMEVALUE(t2)) 주의 : 전각 콜론 "："(U+FF1A)와 반각 ":"(U+003A)은 육안으로 유사하지만 서로 다르다.
SUBSTITUTE로 확실히 치환한다.방법 6: 날짜+시간 텍스트 동시 변환
"2025-10-25 14:23:05"처럼 날짜와 시간이 함께 텍스트인 경우:
=DATEVALUE(TEXTBEFORE(A2," ")) + TIMEVALUE(TEXTAFTER(A2," ")) 서식이 "2025년 10월 25일 오후 2:23"이면 로캘 접두어 제거를 포함한다.
=LET(s,SUBSTITUTE(SUBSTITUTE(A2,"오전 ",""),"오후 ",""), d,DATEVALUE(TEXTBEFORE(s," ")), t,TIMEVALUE(TEXTAFTER(s," ")), IF(ISNUMBER(SEARCH("오후",A2)), d+t+0.5, d+t)) 방법 7: Excel 2016 이전 버전에서 텍스트 나누기
범용 GUI 절차로 빠르게 정규화한다.
- 데이터 범위를 선택한다.
- 데이터 탭 → 텍스트 나누기(구 데이터 마법사)를 클릭한다.
- 구분 기호 사용 또는 너비 고정 선택 후 "다음"을 눌러 콜론 위치를 기준으로 분할한다.
- 열 데이터 형식을 각각 "일반" 또는 "날짜"로 지정한다. 시간만 있으면 "일반"로 두고 완료한다.
- 필요 시
=TIME(B2,C2,D2)로 합쳐 하나의 시간 값으로 만든다.
방법 8: Power Query로 대량 변환·정제 자동화
대량 데이터는 Power Query로 규칙을 한 번 정의하면 반복 적용이 가능하다.
- 데이터 → 데이터 가져오기 → 파일/텍스트/CSV를 선택한다.
- 미리보기에서 열을 선택하고 변환 → 데이터 형식 "시간"을 적용한다.
- 한글 "시/분/초"가 섞이면 변환 → 값 바꾸기에서 "시"→":", "분"→":", "초"→빈값으로 치환한다.
- 고급 열 추가에서 M 코드를 사용해 정규화한다.
// 예시: "오전/오후" 포함 텍스트를 시간으로 = Table.TransformColumns( #"Changed Type", {{"시간열", each let s = Text.Replace(Text.Replace(_, "오전 ", ""), "오후 ", ""), t = Time.FromText(Text.Select(s, {"0".."9", ":"})), add = if Text.StartsWith(_, "오후") then #duration(0,12,0,0) else #duration(0,0,0,0) in DateTime.Time(DateTime.From(#time(Time.Hour(t), Time.Minute(t), Time.Second(t)) + add)) , type time}} ) 쿼리를 닫고 로드하면 항상 동일 규칙으로 재변환한다.
방법 9: 사용자 지정 형식으로 24시간 초과 합계 표시
근무시간 누계, 설비 가동시간 합계는 24시간을 초과한다. 이때 합계 셀 서식을 [h]:mm:ss로 지정한다.
- 합계 셀을 선택한다.
- Ctrl+1 → 사용자 지정.
- 형식 코드에
[h]:mm:ss입력 후 확인한다.
주의 :
hh:mm:ss는 24시간마다 00:00:00으로 롤오버한다. 누적 합계에는 [h] 대괄호가 필요하다.경계값·예외 케이스 처리
1) 24:00, 23:60 같은 비정규 입력
시·분·초가 경계 초과일 때는 정규화 수식을 사용한다.
=LET(s,SUBSTITUTE(A2," ",""), h,VALUE(TEXTBEFORE(s,":")), m,VALUE(TEXTAFTER(s,":")), TIME(h + INT(m/60), MOD(m,60), 0)) 2) 밀리초 포함 "12:30:15.250"
초 소수부를 처리한다.
=LET(t, TEXTSPLIT(A2,":"), h, VALUE(INDEX(t,1)), m, VALUE(INDEX(t,2)), s, VALUE(INDEX(TEXTSPLIT(INDEX(t,3),"."),1)), ms, VALUE(IFERROR(INDEX(TEXTSPLIT(INDEX(t,3),"."),2),0)), h/24 + m/1440 + s/86400 + ms/86400000) 3) AM/PM 대소문자·스페이스 다양성
"am", "PM", 스페이스 유무를 단일 로직으로 흡수한다.
=LET(x,UPPER(TRIM(A2)), y,SUBSTITUTE(SUBSTITUTE(x," AM","")," PM",""), tv,TIMEVALUE(y), IF(RIGHT(x,2)="PM", tv + 0.5, tv)) 4) 데이터 유효성 검사로 잘못된 입력 차단
입력 단계에서 패턴을 제한한다.
- 데이터 유효성 → 사용자 지정.
- 수식:
=AND(ISNUMBER(SEARCH(":",A2)), LEN(A2)>=4, LEN(A2)<=8) 또는 숫자만 허용해 "HHMM" 규칙으로 입력받는다.
=AND(ISNUMBER(A2), A2=INT(A2), A2>=0, A2<=2359, MOD(A2,100)<60) 대량 배치 처리 절차(현업 표준 운영안)
- 원본 시트는 "RAW"로 그대로 보관한다.
- "STG" 시트를 만들고 정제 수식을 적용한다.
- 정제 완료 열을 복사하여 붙여넣기(값)로 확정한다.
- 형식은 "시간" 또는 사용자 지정
hh:mm·hh:mm:ss로 지정한다. - 합계·평균·최댓값 계산 검증을 위해
[h]:mm:ss를 사용한다.
가져오기 단계에서 바로 숫자로 받는 법
CSV·TXT를 불러올 때 마법사에서 열 형식을 지정하면 이후 변환이 불필요하다.
- 데이터 → 텍스트/CSV에서 파일 선택.
- 구분 기호와 로캘을 실제 데이터에 맞게 설정한다.
- 열 데이터 형식을 "시간"으로 지정하거나 "일반"으로 두어 자동 인식을 유도한다.
주의 : 잘못된 로캘을 선택하면 "12.30"이 12.3으로 변하는 등 데이터 손실이 생긴다. 미리보기 숫자 변화를 확인한다.
표시 형식 모음집
| 목적 | 형식 코드 | 예 |
|---|---|---|
| 표준 시간 | hh:mm | 09:30 |
| 초 포함 | hh:mm:ss | 09:30:15 |
| 오전/오후 | AM/PM h:mm | PM 1:05 |
| 누계 시간 | [h]:mm:ss | 37:15:00 |
| 밀리초 | hh:mm:ss.000 | 09:30:15.250 |
오류 진단 플로우
- 셀 정렬 확인: 왼쪽 정렬이면 텍스트일 가능성이 크다.
- 숫자 저장 오류 표시(녹색 표시) 확인 후 "숫자로 변환"을 시험한다.
- 숨은 문자 제거:
TRIM,CLEAN,SUBSTITUTE로 정제한다. - 구분자 파악: 콜론·점·한글 "시/분/초" 여부에 따라 분기한다.
- 정규 수식 적용:
TIMEVALUE또는TIME기반 수식으로 변환한다. - 표시 형식 지정: 목적에 따라
hh:mm:ss또는[h]:mm:ss. - 샘플 계산 검증: 합계·평균이 기대값과 일치하는지 확인한다.
현장에서 바로 쓰는 수식 템플릿
-- 표준 "HH:MM[:SS]" =TIMEVALUE(A2)
-- 한글 "오전/오후 h시 m분"
=LET(s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"시",":"),"분",""),"초",""),
tv,TIMEVALUE(SUBSTITUTE(SUBSTITUTE(s,"오전 ",""),"오후 ","")),
IF(LEFT(A2,2)="오후", tv+0.5, tv))
-- 네 자리 "HHMM"
=TIME(INT(VALUE(A2)/100), MOD(VALUE(A2),100), 0)
-- 점 구분 "HH.MM"
=TIME(VALUE(TEXTBEFORE(A2,".")), VALUE(TEXTAFTER(A2,".")), 0)
-- 날짜+시간 "YYYY-MM-DD HH:MM:SS"
=DATEVALUE(TEXTBEFORE(A2," ")) + TIMEVALUE(TEXTAFTER(A2," "))
-- 숨은 문자 정리 후 변환
=LET(t,SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160)," "),
TIMEVALUE(SUBSTITUTE(SUBSTITUTE(t,"：",":"),"，",":")))
VBA로 초고속 일괄 변환
파일 전체 시트 또는 선택 범위의 텍스트 시간을 숫자 시간으로 변환하는 간단한 매크로이다.
Sub ConvertTextTime() Dim c As Range, s As String, h As Long, m As Long, sec As Double If TypeName(Selection) <> "Range" Then Exit Sub Application.ScreenUpdating = False For Each c In Selection.Cells If Not IsEmpty(c) Then s = Trim(Replace(Replace(Replace(c.Value, "시", ":"), "분", ":"), "초", "")) s = Replace(Replace(Replace(s, "오전 ", ""), "오후 ", ""), "：", ":") If InStr(s, ":") > 0 Then Dim a() As String: a = Split(s, ":") h = Val(a(0)): m = IIf(UBound(a) >= 1, Val(a(1)), 0) sec = IIf(UBound(a) >= 2, Val(a(2)), 0) c.Value = h / 24 + m / 1440 + sec / 86400 ElseIf IsNumeric(s) Then Dim n As Long: n = CLng(s) h = n \ 100: m = n Mod 100 c.Value = h / 24 + m / 1440 End If End If Next c Selection.NumberFormat = "hh:mm:ss" Application.ScreenUpdating = True End Sub 주의 : 매크로는 되돌리기 어렵다. 실행 전 범위를 복사해 백업 시트에 붙여넣기(값)로 보관한다.
품질 보증 체크
- 표본 30개 이상에 대해 수동 계산과 비교한다.
- 경계값(00:00, 12:00, 23:59, 24:00 유사 데이터)을 포함한다.
- 표시 형식을 바꾸어도 값이 변하지 않는지 확인한다.
- 누계 합계는
[h]:mm:ss로 검증한다.
자주 묻는 실수와 반례
--A2로 강제 변환이 안 되는 이유: 콜론 포함 텍스트는 일반 숫자가 아니다.- 합계가 0으로 보이는 이유: 표시 형식이 일반 숫자이거나 자리 폭이 좁아 "####"로 보일 수 있다.
- 세미콜론 구분 "12;30": 데이터 원천에서 콜론으로 교체 후 변환한다.
- 전각 숫자 "０９：３０": 전각→반각 치환을 먼저 수행한다.
검증용 테스트 데이터 세트
텍스트, 기대 결과(표시 hh:mm:ss) "09:30", 09:30:00 "9시 30분", 09:30:00 "오후 1:05", 13:05:00 "930", 09:30:00 "12.30", 12:30:00 "2025-10-25 23:59:59", 23:59:59 "12:30:15.250", 12:30:15 "０８：０５", 08:05:00 "오전 12:00", 00:00:00 "오후 12:00", 12:00:00 현업 적용 시나리오
콜센터 근무 스케줄
ERP에서 "오전/오후" 표기와 "HHMM" 숫자가 혼합되어 들어오는 경우가 많다. STG 시트에 변환 수식을 표준화하고, 원본을 참조하는 구조로 유지하여 재로딩에도 안전하게 운용한다.
설비 로그
밀리초가 포함된 IoT 로그는 초 소수부 수식을 적용한다. 누계 가동시간은 [h]:mm:ss로 표시한다.
운송 배차
점(.) 구분 데이터를 변환한 뒤, 24시간 표기 맞춤을 위해 사용자 지정 형식을 사용한다.
문제 해결 요약
- 텍스트 정제:
TRIM,CLEAN,SUBSTITUTE. - 구분자 통일: "시/분/초", 전각 콜론을 콜론으로 통일.
- 형태별 변환:
TIMEVALUE,TIME, 분리·조합. - 날짜+시간은 각각
DATEVALUE와TIMEVALUE로 분리 후 합산. - 표시 형식 확정:
hh:mm:ss또는[h]:mm:ss. - 대량 처리: Power Query 또는 VBA 자동화.
FAQ
엑셀에서 "12:30"이 있는데 합계가 안 맞는다. 왜 그런가?
텍스트일 가능성이 크다. =ISNUMBER(A2)로 확인한다. FALSE면 =TIMEVALUE(A2)로 변환한다.
합계가 24시간 넘으면 00:00으로 보인다.
표시 형식을 [h]:mm:ss로 바꾼다. 값은 올바르나 표시만 잘못된 경우이다.
"오후 12:00"은 24:00인가 12:00인가?
12:00이다. 오후 12시는 정오로 12:00이며, 오전 12시는 00:00이다.
CSV 불러오면 전부 텍스트가 된다. 방지법은?
가져오기 마법사에서 로캘과 열 형식을 지정한다. 또는 Power Query에서 열 형식 "시간"으로 설정한다.
"930"을 "9:30"으로 바꿨더니 앞의 0이 사라진다.
표시 형식을 hh:mm로 설정한다. 값 자체는 시간 분수이므로 9시 30분이 유지된다.
텍스트가 섞여 정렬이 이상하다.
정규화 열을 만들어 모두 숫자 시간으로 변환한 후 그 열로 정렬한다.