업무 데이터를 분석할 때, 엑셀에서 날짜 값을 활용하여 “오늘 실적은 얼마인가?” 또는 “지난주 같은 요일과 비교했을 때 어떤 변화가 있는가?”와 같은 질문에 대한 답을 찾아야 할 때가 많습니다. 그러나 엑셀이 값을 날짜로 인식하지 못하면 분석에 어려움을 겪을 수 있습니다.
이러한 문제는 특히 여러 사용자가 데이터를 입력하거나, 다른 시스템에서 복사하여 붙여넣거나, 데이터베이스에서 가져올 때 자주 발생합니다.
본 글에서는 텍스트로 저장된 날짜 값을 실제 날짜 형식으로 변환하는 네 가지 시나리오와 그 해결 방법에 대해 자세히 알아보겠습니다.
점(.)으로 구분된 날짜 변환
엑셀 초보자들이 흔히 저지르는 실수 중 하나는 날짜를 입력할 때 일, 월, 년도를 구분하기 위해 마침표(.)를 사용하는 것입니다.
엑셀은 이러한 형식을 날짜 값으로 인식하지 않고 텍스트로 저장합니다. 하지만 ‘찾기 및 바꾸기’ 기능을 이용하면 간단히 해결할 수 있습니다. 마침표를 슬래시(/)로 바꾸면 엑셀은 자동으로 해당 값을 날짜로 인식합니다.
먼저, 수정할 열을 선택합니다.
그런 다음, ‘홈’ 탭에서 ‘찾기 및 선택’ > ‘바꾸기’를 클릭하거나 단축키 Ctrl+H를 누릅니다.
‘찾기 및 바꾸기’ 창에서 ‘찾을 내용’ 필드에 마침표(.)를 입력하고, ‘바꿀 내용’ 필드에 슬래시(/)를 입력합니다. 마지막으로 ‘모두 바꾸기’를 클릭합니다.
이제 모든 마침표가 슬래시로 바뀌었고, 엑셀은 새로운 형식을 날짜로 올바르게 인식합니다.
만약 스프레드시트 데이터가 정기적으로 변경되고 자동화된 해결책이 필요하다면, SUBSTITUTE 함수를 활용할 수 있습니다.
=VALUE(SUBSTITUTE(A2,".","/"))
SUBSTITUTE 함수는 텍스트를 처리하는 함수이므로 그 자체로는 날짜로 변환할 수 없습니다. VALUE 함수는 텍스트 값을 숫자 값으로 변환하여 이 문제를 해결합니다.
결과는 다음과 같습니다. 이제 값은 날짜 형식으로 지정해야 합니다.
이 작업은 ‘홈’ 탭의 ‘숫자 형식’ 목록에서 수행할 수 있습니다.
여기서는 점 구분 기호의 예시를 들었지만, 동일한 기법을 사용하여 다른 구분 기호를 대체하거나 변경할 수 있습니다.
YYYYMMDD 형식 변환
다음과 같이 YYYYMMDD 형식으로 날짜를 받는 경우, 다른 접근 방식이 필요합니다.
이 형식은 여러 국가에서 날짜 값을 저장하는 방식에 대한 혼란을 없애주기 때문에 기술적으로 표준으로 여겨집니다. 하지만 엑셀은 이 형식을 바로 인식하지 못합니다.
빠르게 수동으로 해결하려면 ‘텍스트 나누기’ 기능을 사용할 수 있습니다.
변환하려는 값의 범위를 선택한 후, ‘데이터’ > ‘텍스트 나누기’를 클릭합니다.
‘텍스트 나누기 마법사’가 나타납니다. 처음 두 단계에서 ‘다음’을 클릭하면 세 번째 단계로 넘어갑니다. 여기서 ‘날짜’를 선택한 다음, 드롭다운 목록에서 현재 셀에 사용 중인 날짜 형식을 선택합니다. 이 예에서는 ‘YMD’ 형식을 사용합니다.
만약 수식을 활용한 해결책을 원한다면, 날짜 함수를 사용하여 날짜를 재구성할 수 있습니다.
텍스트 함수인 LEFT, MID, RIGHT를 함께 사용하여 셀 내용에서 날짜의 세 부분(일, 월, 년도)을 추출합니다.
다음 수식은 샘플 데이터를 사용하여 이를 설명합니다.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
이러한 기술 중 하나를 사용하여 8자리 숫자 값을 변환할 수 있습니다. 예를 들어, 날짜가 DDMMYYYY 형식 또는 MMDDYYYY 형식으로 제공될 수도 있습니다.
DATEVALUE 및 VALUE 함수 활용
경우에 따라 문제는 구분 기호 때문이 아니라 날짜 구조가 어색하거나 텍스트로 저장되어 있기 때문에 발생합니다.
아래에는 다양한 구조의 날짜 목록이 있지만, 모두 날짜로 인식할 수 있습니다. 하지만 텍스트로 저장되어 변환이 필요합니다.
이러한 상황에서는 여러 기술을 사용하여 쉽게 변환할 수 있습니다.
본 글에서는 이러한 상황을 처리하는 데 유용한 두 가지 함수, DATEVALUE와 VALUE를 소개하고자 합니다.
DATEVALUE 함수는 텍스트를 날짜 값으로 변환하고, VALUE 함수는 텍스트를 일반 숫자 값으로 변환합니다. 두 함수의 차이는 미미합니다.
위의 이미지에 있는 값 중 하나에는 시간 정보도 포함되어 있습니다. 이 부분이 두 함수의 미세한 차이를 보여줄 것입니다.
다음은 DATEVALUE 함수를 사용하여 각각의 값을 날짜 값으로 변환한 결과입니다.
=DATEVALUE(A2)
결과에서 4행의 시간 정보가 제거된 것을 확인할 수 있습니다. 이 함수는 날짜 값만 반환하며, 결과는 여전히 날짜 형식으로 지정해야 합니다.
다음은 VALUE 함수를 사용한 결과입니다.
=VALUE(A2)
이 함수는 4행을 제외하고 동일한 결과를 생성합니다. 즉, 시간 값도 유지됩니다.
그런 다음 결과를 날짜 및 시간 형식으로 지정하거나, 시간 값을 숨기는 날짜 형식으로 지정할 수 있습니다(시간 값은 제거할 수 없음).