숫자로만 된 날짜+시간을 날짜, 시간 형식으로 바꾸기

아래와 같이 날짜 및 시간이 연속해서 연결되어 있는 경우 엑셀에서는 날짜, 시간으로 인식할 수 없으므로 

하이픈, 콜론이 없는 날짜, 시간

문자 형식 날짜,시간.xlsx

0.01MB

 

 

엑셀에서 인식할 수 있는 yyyy-mm-dd hh:mm:ss로 바꿔줘야 합니다.

A2셀의 숫자를 보면 20200725가 날짜이고, 14가 시간에 해당됩니다.

 

1. 날짜 변환을 위한 함수

날짜 변환을 위한 함수는 Date와 DateValue가 있습니다.

Date와 DateSerial함수의 인수는 둘 다 year, month, day이고,

DateValue의 인수 date_text, 다시 말해 날짜 형식의 텍스트입니다.

 

2. 시간 변환을 위한 함수

시간 변환을 위한 함수는 Time과 TimeValue가 있습니다.

Time 함수의 인수는 hour, minute, second이고,

TimeValue의 함수는 시간 형식의 텍스트입니다.

 

3. 날짜+시간 숫자에서 날짜와 시간 구하기

가. 날짜 구하기

(1) Date 함수

날짜는 A2셀(2022072514)을 기준으로 하면 앞 4자리가 연도이고, 그다음 2자리가 월, 그다음 2자리가 일이 됩니다.

따라서, 수식은 =DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))가 됩니다.

Left함수는 A2셀에서 왼쪽 4글자를 가져오므로 2022가 되며,

Mid함수는 A2셀에서 시작위치부터 개수를 가져오므로, Mid(A2,5,2)는 A2셀에서 5번째부터 2개를 가져오므로 07이 되고,

Mid(A2,7,2)는 A2셀에서 7번째부터 2개를 가져오므로 25를 가져옵니다.

따라서 이를 Date함수의 인수로 넣으면 연,월,일이 되므로 2022-07-25가 됩니다.

Date함수 이용하여 숫자를 날짜 형식으로 변환

 

(2) DateValue 함수

DateValue 함수의 인수는 날짜 형식의 텍스트이므로 20200725를 2020-07-25로 바꿔야 합니다.

따라서, 연,월,일을 구한 다음 -으로 연결하는데 연결 연산자는 &이므로

연&"-"&월&"-"&일이 됩니다.

이를 수식에 적용하면 =DATEVALUE(LEFT(A2,4)&"/"&MID(A2,5,2)&"/"&MID(A2,7,2))이 되는데, 날짜 형식으로 표시되지 않고, 1900-1-1부터 연속된 숫자 44037로 표시됩니다.

DateValue 함수를 이용하여 날짜 텍스트를 날짜 형식으로 변환

 

따라서 홈 탭 > 표시 형식 그룹에서 일반을 간단한 날짜로 변경합니다.

간단한 날짜 표시 형

 

그러면 2020-07-25로 표시됩니다.

 

나. 시간 구하기

(1) Time 함수

Time함수의 인수는 시,분,초인데, A2셀을 보면 2020072514로 시간만 있습니다. 따라서, 분과 초는 0으로 입력하면 됩니다. 그러나, 분과 초를 입력하지 않으면 "너무 적은 인수를 입력했다"라고 에러가 표시됩니다

시간은 A2셀에서 오른쪽 숫자 2개이므로 right(a2,2)로 입력합니다.

완성된 수식 =TIME(RIGHT(A2,2),0,0)입니다.

time 함수를 이용하여 숫자를 시간으로 변환

 

시간이 2:00 PM으로 표시되는데, 시:분:초 형식으로 바꾸려면

Ctrl + 1을 누른 다음 표시형식 - 사용자 지정에서 h:mm:ss를 클릭하면 됩니다. h는 시간이 한 자리일 경우는 한 자리, 두 자리일 경우는 2자리로 표시하는 것으로 항상 2자리로 표시하려면 형식 입력 칸에서 h를 hh로 수정하면 됩니다.

시간 표시 형식 지정

 

h:mm:ss로 형식을 바꾸면 아래와 같이 표시됩니다.

h:mm:ss의 시간 표시 형식

 

(2) TimeValue 함수

TimeValue의 인수는 시간 형식의 문자이므로 14:00:00식으로 입력해야 합니다.

따라서, 수식은 =TIMEVALUE(RIGHT(A2,2)&":"&"0:0")이 되는데, 시:분:초가 아니라 0.583333으로 표시됩니다. 이것은 14시/24시의 값입니다.

TimeValue 함수를 이용하여 숫자를 시간으로 변환(소숫점이하가 있는 실수로 표시됨)

 

시:분:초로 바꾸려면 사용자 지정 서식에서 h:mm:ss로 바꿔야 하고,

홈 탭 > 표시 형식 그룹에서 일반을 간단한 시간으로 바꾸면

시간 표시 형식

 

오후 2:00:00으로 표시돼서 time함수로 시간을 구할 때와 PM과 오후로 다릅니다.

 

다. 날짜와 시간 표시 하기

(1) 결합 연산자(&)로 연결하기(x)

날짜와 시간이므로 날짜 & " " & 시간으로 해도 될 것 같아서,

B2셀에 =C2&" "&E2라고 입력하면

날짜와 시간을 & 연산자로 연결(X)

 

날짜와 시간 사이에 공백 한 칸이 들어가서 숫자와 소수점이 있는 실수가 연결된 형태로 보입니다.

간단한 날짜 형식으로 변환해도 날짜로 표시되지 않습니다.

따라서 결합 연산자를 이용해서는 안됩니다.

 

(2) + 로 연결하기(날짜와 시간 더하기)(O)

날짜나 시간 모두 숫자이므로 더하기를 해서 =C2+E2라고 입력하면

날짜, 시간 형태로 제대로 표시됩니다.

날짜와 시간을 같이 표시하려면 날짜와 시간을 더해야 함

 

C2셀과 E2셀을 더하지 않고, 각각의 수식을 더하는 식으로 표시하면

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(RIGHT(A2,2),0,0)이 되며, 결괏값은 같습니다.

Data함수 + Time 함수

 

이제 B2셀의 채우기 핸들을 더블 클릭하면 B8셀까지 수식이 복사됩니다.

채우기 핸들을 이용한 수식 복사