Excel에서 텍스트로 서식이 지정된 날짜를 실제 날짜로 변환하는 방법에는 여러 가지가 있으며 이 자습서에서는 모든 방법을 탐색하는 것을 목표로 합니다.
Excel로 데이터를 가져올 때 Excel에서 인식하지 못하는 다양한 형식이 나타날 수 있습니다. 때때로 외부 소스에서 날짜를 가져올 때 텍스트 형식으로 제공됩니다.
Excel이 시스템 지역 설정에 따라 날짜 형식을 적용한다는 사실을 아는 것도 중요합니다. 따라서 다른 국가에서 가져온 데이터를 Excel로 가져올 때 Excel에서 해당 데이터를 인식하지 못하고 텍스트 항목으로 저장할 수 있습니다.
이 문제가 있는 경우 Excel에서 텍스트를 날짜로 변환하는 데 사용할 수 있는 여러 가지 방법이 있으며 이 자습서에서 모두 다룰 것입니다.
텍스트를 날짜로 변환하는 방법
워크시트에 실제 날짜 대신 텍스트 형식의 날짜가 포함되어 있으면 계산에 사용할 수 없습니다. 따라서 실제 날짜로 다시 변환해야 합니다.
날짜가 왼쪽에 정렬되어 있으면 텍스트가 기본적으로 왼쪽에 정렬되기 때문에 텍스트 형식이 지정되었음을 의미합니다. 그리고 숫자와 날짜는 항상 오른쪽으로 정렬됩니다.
Excel에서 텍스트를 날짜로 변환하는 몇 가지 방법이 있습니다.
- 오류 검사 옵션
- Excel 텍스트를 열로 변환 기능
- 찾기 및 바꾸기
- 특수 도구 붙여넣기
- Excel 수식 및 함수
오류 검사 옵션을 사용하여 텍스트를 날짜로 변환
Excel에는 데이터에서 명백한 오류를 찾아내는 오류 검사 기능이 내장되어 있습니다. 오류가 발견되면 오류가 있는 셀의 왼쪽 상단 모서리에 작은 녹색 삼각형(오류 표시기)이 표시됩니다. 해당 셀을 선택하면 노란색 느낌표가 있는 주의 표시가 나타납니다. 커서를 해당 기호 위로 이동하면 Excel에서 해당 셀의 가능한 문제에 대해 알려줍니다.
예를 들어 날짜에 두 자리 형식으로 연도를 입력하면 Excel은 해당 날짜를 텍스트로 가정하고 텍스트로 저장합니다. 해당 셀을 선택하면 '이 셀에는 연도의 2자리 숫자로만 표시되는 날짜 문자열이 포함되어 있습니다'라는 경고와 함께 느낌표가 나타납니다.
셀에 해당 오류 표시기가 표시되면 느낌표를 클릭하면 텍스트 형식의 날짜를 실제 날짜로 변환하는 몇 가지 옵션이 표시됩니다. Excel은 19XX 또는 20XX(1915의 경우 19XX, 2015의 경우 20XX)로 변환하는 옵션을 표시합니다. 적절한 옵션을 선택하십시오.
그런 다음 텍스트가 적절한 날짜 형식으로 변환됩니다.
Excel에서 오류 검사 옵션을 활성화하는 방법
일반적으로 오류 검사 옵션은 기본적으로 Excel에서 켜져 있습니다. 오류 검사 기능이 작동하지 않으면 Excel에서 오류 검사를 활성화해야 합니다.
이렇게 하려면 '파일' 탭'을 클릭하고 왼쪽 패널에서 '옵션'을 선택합니다.
Excel 옵션 창에서 왼쪽 패널의 '수식'을 클릭하고 오른쪽 패널의 오류 검사 섹션에서 '백그라운드 오류 검사 활성화'를 활성화합니다. 그리고 오류 검사 규칙 섹션에서 '2자리 숫자로 표시된 연도를 포함하는 셀'을 선택합니다.
Excel 텍스트를 열 기능으로 사용하여 텍스트를 날짜로 변환
텍스트를 열로 변환은 데이터를 여러 열로 분할할 수 있는 Excel의 훌륭한 기능이며 텍스트 값을 날짜 값으로 변환하는 강력한 도구이기도 합니다. 이 방법은 여러 다른 데이터 형식을 인식하고 적절한 날짜 형식으로 변환합니다.
간단한 텍스트 문자열을 날짜로 변환
날짜가 다음과 같은 텍스트 문자열 형식으로 지정되어 있다고 가정해 보겠습니다.
Text to Columns 마법사를 사용하여 모든 항목을 날짜로 빠르게 다시 포맷할 수 있습니다.
먼저 날짜로 변환할 텍스트 항목의 범위를 선택합니다. 그런 다음 리본의 '데이터' 탭으로 이동하고 데이터 도구 그룹에서 '텍스트를 열로' 옵션을 클릭합니다.
텍스트를 열로 변환 마법사가 나타납니다. Text to column 마법사의 1단계에서 Original data type 아래의 'Delimited' 옵션을 선택하고 'Next'를 클릭합니다.
2단계에서 모든 '구분 기호' 상자를 선택 취소하고 다음을 클릭합니다.
마법사의 마지막 단계에서 열 데이터 형식에서 '날짜'를 선택하고 '날짜' 옆의 드롭다운 목록에서 날짜 형식을 선택한 다음 '마침' 버튼을 클릭합니다. 우리의 경우 "01 02 1995"(일 월 연도)로 표시된 텍스트 날짜를 변환하므로 'Date:' 드롭다운 목록에서 'DMY'를 선택합니다.
이제 Excel은 텍스트 날짜를 실제 날짜로 변환하고 셀에 오른쪽 정렬로 표시합니다.
메모: Text to Column 기능을 사용하기 전에 모든 텍스트 문자열이 동일한 형식인지 확인하십시오. 그렇지 않으면 텍스트가 변환되지 않습니다. 예를 들어, 텍스트 날짜 중 일부는 월/일/년(MDY) 형식과 같은 형식이고 다른 날짜는 일/월/년(DMY)인 경우 3단계에서 'DMY'를 선택하면 잘못된 결과를 얻게 됩니다. 아래 그림과 같이.
복잡한 텍스트 문자열을 날짜로 변환
Text to Columns 기능은 복잡한 텍스트 문자열을 날짜로 변환하려는 경우에 유용합니다. 구분 기호를 사용하여 데이터를 분할하고 2개 이상의 열에 표시해야 하는 위치를 식별할 수 있습니다. 그리고 DATE 함수를 사용하여 날짜의 분할 부분을 전체 날짜로 결합합니다.
예를 들어 날짜가 다음과 같이 여러 부분으로 구성된 텍스트 문자열로 표시되는 경우:
2020년 2월 1일 수요일
2020년 2월 1일 오후 4시 10분
Text to column 마법사를 사용하여 쉼표로 구분된 날짜, 날짜 및 시간 정보를 구분하고 여러 열에 표시할 수 있습니다.
먼저 날짜로 변환할 모든 텍스트 문자열을 선택합니다. '데이터' 탭에서 '텍스트를 열로' 버튼을 클릭합니다. Text to column 마법사의 1단계에서 Original data type 아래의 'Delimited' 옵션을 선택하고 'Next'를 클릭합니다.
마법사의 2단계에서 텍스트 문자열에 포함된 구분 기호를 선택하고 '다음'을 클릭합니다. 쉼표와 공백으로 구분된 예제 텍스트 문자열 – "Monday, February 01, 2015, 1:00 PM". 텍스트 문자열을 여러 열로 분할하려면 '쉼표'와 '공백'을 구분 기호로 선택해야 합니다.
마지막 단계에서 데이터 미리보기 섹션의 모든 열에 대해 '일반' 형식을 선택합니다. '대상' 필드에 열을 삽입할 위치를 지정합니다. 지정하지 않으면 원본 데이터를 덮어씁니다. 원본 데이터의 일부를 무시하려면 데이터 미리 보기 섹션에서 해당 데이터를 클릭하고 '열을 가져오지 않음(건너뛰기)' 옵션을 선택합니다. 그런 다음 '마침'을 클릭하십시오.
이제 날짜 부분(요일, 월, 연도, 시간)이 B, C, D, E, F 및 G 열로 분할됩니다.
그런 다음 DATE 수식을 사용하여 날짜 부분을 결합하여 전체 날짜를 얻습니다.
Excel DATE 함수의 구문:
=DATE(년,월,일)이 예에서 월, 일 및 연도 부분은 각각 C, D 및 E 열에 있습니다.
DATE 함수는 텍스트가 아닌 숫자만 인식합니다. C 열의 월 값은 모두 텍스트 문자열이므로 숫자로 변환해야 합니다. 그렇게 하려면 MONTH 함수를 사용하여 월 이름을 월 숫자로 변경해야 합니다.
월 이름을 월 숫자로 변환하려면 DATE 함수 내에서 다음 MONTH 함수를 사용하십시오.
=MONTH(1&C1)MONTH 함수는 월 이름을 해당 월 번호로 변환하기 위해 월 이름이 포함된 셀 C2에 1을 추가합니다.
이것은 다른 열의 날짜 부분을 결합하는 데 사용해야 하는 DATE 함수입니다.
=날짜(E1,MONTH(1&C1),D1)
이제 수식 셀의 하단 모서리에 있는 채우기 핸들을 사용하고 수식을 열에 적용합니다.
찾기 및 바꾸기 메서드를 사용하여 텍스트를 날짜로 변환
이 메서드는 구분 기호를 사용하여 텍스트 형식을 날짜로 변경합니다. 날짜의 일, 월 및 연도가 대시(-) 또는 슬래시(/)가 아닌 다른 구분 기호로 구분되면 Excel에서 날짜로 인식하지 못하고 텍스트로 저장합니다.
이 문제를 해결하려면 찾기 및 바꾸기 기능을 사용하십시오. 비표준 마침표 구분 기호(.)를 슬래시(/) 또는 대시(-)로 변경하면 Excel에서 자동으로 값을 날짜로 변경합니다.
먼저 날짜로 변환하려는 모든 텍스트 날짜를 선택합니다. '홈' 탭에서 리본의 맨 오른쪽 모서리에 있는 '찾기 및 선택' 버튼을 클릭하고 '바꾸기'를 선택합니다. 또는 Ctrl+H 찾기 및 바꾸기 대화 상자를 엽니다.
찾기 및 바꾸기 대화 상자에서 텍스트에 포함된 구분 기호(이 경우 '찾을 내용' 필드에 마침표(.))를 입력하고 '바꿀 내용' 필드에 슬래시(/) 또는 대시(-)를 입력합니다. 구분자를 바꾸려면 '모두 바꾸기' 버튼을 클릭하고 창을 닫으려면 '닫기'를 클릭합니다.
이제 Excel은 텍스트 문자열이 이제 날짜임을 인식하고 자동으로 날짜 형식을 지정합니다. 날짜는 아래와 같이 오른쪽으로 정렬됩니다.
특수 붙여넣기 도구를 사용하여 텍스트를 날짜로 변환
텍스트 문자열을 날짜로 변환하는 또 다른 빠르고 쉬운 방법은 특수 붙여넣기 옵션을 사용하여 텍스트 문자열에 0을 추가하는 것입니다. 값에 0을 추가하면 텍스트가 날짜 형식을 지정할 수 있는 날짜의 일련 번호로 변환됩니다.
먼저 빈 셀을 선택하고 복사합니다(선택하고 Ctrl + C 복사).
그런 다음 변환하려는 텍스트 날짜가 포함된 셀을 선택하고 마우스 오른쪽 버튼을 클릭한 다음 '선택하여 붙여넣기' 옵션을 선택합니다.
선택하여 붙여넣기 대화 상자의 붙여넣기 섹션에서 '모두'를 선택하고 작업 섹션에서 '추가'를 선택한 다음 '확인'을 클릭합니다.
대상 셀의 값을 붙여넣은 값으로 빼기/곱하기/나누는 다른 산술 연산을 수행할 수도 있습니다(예: 셀에 1을 곱하거나 1로 나누거나 0을 빼기).
작업에서 '추가'를 선택하면 선택한 모든 텍스트 날짜에 '0'이 추가됩니다. '0'을 추가해도 값이 변경되지 않기 때문에 각 날짜의 일련 번호를 얻게 됩니다. 이제 셀 형식을 변경하기만 하면 됩니다.
일련 번호를 선택하고 '홈' 탭의 숫자 그룹에서 '숫자 형식' 드롭다운 목록을 클릭합니다. 드롭다운에서 '짧은 날짜' 옵션을 선택합니다.
이제 볼 수 있듯이 숫자는 날짜 형식이 지정되고 오른쪽으로 정렬됩니다.
수식을 사용하여 텍스트를 날짜로 변환
텍스트를 날짜로 변환하는 데 주로 사용되는 함수는 DATEVALUE 및 VALUE입니다.
Excel DATEVALUE 함수 사용
Excel DATEVALUE 함수는 텍스트로 표시된 날짜를 날짜의 일련 번호로 변환하는 가장 쉬운 방법 중 하나입니다.
DATEVALUE 함수의 구문:
=DATEVALUE(날짜_텍스트)논쟁: 날짜_텍스트 텍스트 날짜가 포함된 셀을 숨기거나 참조할 텍스트 문자열을 지정합니다.
공식:
=DATEVALUE(A1)다음 그림은 DATEVALUE 함수가 텍스트로 저장된 몇 가지 다른 날짜 형식을 처리하는 방법을 보여줍니다.
날짜 일련 번호를 얻었으므로 이제 이 숫자에 날짜 형식을 적용해야 합니다. 그렇게 하려면 일련 번호가 있는 셀을 선택한 다음 '홈' 탭으로 이동하여 '숫자 형식' 드롭다운 목록에서 '간단한 날짜'를 선택합니다.
이제 C 열에 형식이 지정된 날짜가 있습니다.
텍스트 날짜(A8)에 연도 부분이 없더라도 DATEVALUE는 컴퓨터 시계의 현재 연도를 사용합니다.
DATEVALUE 함수는 날짜처럼 보이는 텍스트 값만 변환합니다. 숫자와 유사한 텍스트를 날짜로 변환하거나 숫자 값을 날짜로 변경할 수 없으므로 Excel의 VALUE 함수가 필요합니다.
엑셀 VALUE 함수 사용하기
Excel VALUE 함수는 날짜나 숫자와 유사한 텍스트 문자열을 숫자 값으로 변환할 수 있으므로 날짜뿐만 아니라 숫자를 변환할 때 정말 유용합니다.
VALUE 함수:
=VALUE(텍스트)텍스트– 변환하려는 텍스트 문자열 또는 텍스트 문자열이 포함된 셀을 참조합니다.
텍스트 날짜를 변환하는 예제 수식:
=값(A1)아래 VALUE 수식은 날짜처럼 보이는 모든 텍스트 문자열을 아래와 같이 숫자로 변경할 수 있습니다.
그러나 VALUE 함수는 모든 유형의 날짜 값을 지원하지 않습니다. 예를 들어 날짜가 소수점 이하 자릿수(A11)를 사용하는 경우 #VALUE! 오류.
날짜 일련 번호가 있으면 DATEVALUE 함수에 대해 수행한 것처럼 날짜 일련 번호로 셀 형식을 날짜처럼 보이게 해야 합니다. 그렇게 하려면 일련 번호를 선택하고 '홈' 탭의 '숫자 형식' 드롭다운 메뉴에서 '날짜' 옵션을 선택합니다.
그게 다야, 이것들은 Excel에서 텍스트로 서식이 지정된 날짜를 날짜로 변환할 수 있는 5가지 다른 방법입니다.