엑셀 DATEVALUE 함수 & DATE 함수로 텍스트를 날짜로 변환

연습용 파일(노란색 영역에서 함수 연습, 시트별 연습)

 

날짜형식변환_연습.xlsx

0.01MB

 

엑셀 텍스트를 날짜로 변환 시에 활용하는 함수: DATEVALUE, DATE 함수

엑셀 작업을 하다 보면 텍스트로 표시된 날짜를 날짜형식으로 바꿔야 할 때가 있다. "12월 23일", "2023/12/23" 등 텍스트 형식으로 날짜를 표시하는 방법은 다양하지만 기본적인 텍스트함수와 DATEVALUE, DATE 함수만 제대로 구사한다면 어떤 데이터를 만나도 쉽게 날짜 형식으로 데이터를 변환할 수 있다. 기본적인 텍스트 함수는 엑셀 텍스트함수 기초: LEFT RIGHT MID SUBSTITUTE를 참조하면 되고 DATEVALUE 함수와 DATE 함수만 살펴보겠다.

 

DATEVALUE와 DATE 함수

DATEVALUE 함수는 년-월-일로 된 하나의 텍스트를 함수에 넣어서 날짜를 반환하는 함수이고, DATE 함수는 년, 월, 일 각각 함수에 넣어서 날짜를 반환하는 함수이다.

그림1: DATEVALUE 함수와 DATE 함수 사용 예시

날짜의 연도, 월, 일이  "/", "-" 정도로 구분되어 있으면 해당 텍스트를 그대로 DATEVALUE 함수에 넣어주면 날짜값이 반환된다. 하지만 콤마(";"), 점(".") 같은 문자로 구분이 되어 있거나 아예 구분이 안 되어 있는 경우 함수에 에러가 발생하는 것을 그림 1에서 확인할 수 있다.

이런 경우에는 SUBSTITUTE 함수를 사용해서 콤마(";"), 점(".")을 "-"나 "/"로 바꾸거나 DATE 함수를 사용해서 연, 월, 일을 각각 넣어주면 해결을 할 수 있다. 여기서 주의할 점은 연도를 입력할 때 4자리로 입력해줘야 한다는 것이다. 위 그림 1의 마지막 예시에서 연도를 2023이 아닌 23으로 넣어준 결과 1923년으로 인식하는 것을 확인할 수 있다.

엑셀 텍스트 날짜서식변환 예제

년, 월, 일이 구분된 데이터

년, 월, 일이 구분된 데이터는 그림 2와 같이 DATEVALUE 함수를 써서 날짜로 쉽게 변환할 수 있다. 다만 년, 월, 일이 콤마(";"), 점(".") 등으로 구분된 데이터는 SUBSTITUTE를 통해서 콤마(";"), 점(".")을 "/"나 "-"로 바꾼 뒤에 DATEVALUE를 적용하면 날짜값을 반환받을 수 있다.

그림2: DATEVALUE 함수사용 예시

년, 월, 일이 구분되지 않은 비정형적 데이터

그림 3처럼 년, 월, 일이 구분되지 않는 특이한 모양의 데이터는 년, 월, 일 데이터를 각각 만들어서 DATE 함수에 넣어주면 날짜로 변환이 가능하다. LETF, MID, RIGHT, SUBSTITUTE 정도의 함수를 사용해서 년, 월, 일을 구성하고 각각의 년, 월, 일을 DATE 함수에 넣어주면 날짜값을 얻을 수 있는 것이다.

그림3: DATE 함수사용 예시