엑셀에서 문자열로된 날짜를 날짜 서식으로 변경하기
안녕하세요. 주식관련 지식을 공유하는 인천고래입니다.
주식 통계를 내다보니 가끔 엑셀 파일을 다룰 때가 있는데요.
이때 파이썬에서 추출되는 문자열이 엑셀에서 날짜로 인식을 못해서 필터링이 제대로 안 되는 경우가 많습니다.
해서 저도 고생고생하며 알아낸(다른 분들은 알고 계실수도 있는) 방법을 공유 드립니다.
위의 이미지에서 보듯이 Excel에서 YYYY-MM-DD 형식의 문자열을 날짜로 변환하려고 하지만, 서식을 '날짜'로 변경해도 인식되지 않아서 왼쪽 처럼 계속 보이는 경우가 있습니다.
이 문제는 데이터가 실제 날짜가 아니라 텍스트 형식으로 저장되어 있기 때문입니다.
특히 F2를 누른 후 Enter를 입력해야만 오른쪽 이미지와 같이 날짜로 변환되는 경우가 있는데, 수천 개의 데이터를 하나하나 변환하는 것은 비효율적입니다. 이 글에서는 효율적으로 문자열을 날짜로 변환하는 방법을 단계별로 소개합니다.
1. 문제 상황(날짜 데이터가 문자열 인 경우)
Excel에서 YYYY-MM-DD 형식의 데이터를 불러왔을 때, 다음과 같은 현상이 발생할 수 있습니다:
- 셀을 선택하면 수식 입력줄에 값이 보이지만 왼쪽 정렬되어 있음 (즉, 텍스트로 인식됨)
- 셀 서식을 '날짜'로 변경해도 적용되지 않음
- F2를 누른 후 Enter를 입력하면 오른쪽 정렬되면서 날짜로 변환됨
이제 이런 문제를 해결하는 아래와 같이 3가지 방법이 있습니다.
| 텍스트 나누기 | 여러 개의 데이터를 한 번에 변환 가능 |
| DATE 함수 사용 | 변환 후 원본 데이터를 유지 가능 |
| 찾기 및 바꾸기 | 빠르고 간단하게 변환 가능 |
다만, 방법은 위와 같이 3가지 방법이 존재하는데 제일 확실한 방법은 두 번째 DATE 함수를 사용하는 것입니다.
다른 방법들은 제외하고 함수를 사용해서 변경하는 방법을 알려드리겠습니다.
2. 해결 방법 (함수 사용하기)
Excel의 DATE 함수를 활용하면 수식을 통해 문자열을 날짜로 변환할 수 있습니다.
📌 예제
- 셀 A1에 **"2022-03-30"**이 입력되어 있다고 가정할 때, 아래 수식을 입력합니다.
(날짜 데이터가 B2에 있으면 A1을 B2로 바꾸셔야 합니다.) - =DATE(LEFT(A1,4), MID(A1,6,2), RIGHT(A1,2))
📌 수식 설명
- LEFT(A1,4): 연도(YYYY)를 추출
- MID(A1,6,2): 월(MM)을 추출
- RIGHT(A1,2): 일(DD)을 추출
- DATE(연도, 월, 일): 추출한 값을 이용해 날짜 변환
변환된 날짜 데이터를 복사한 후 **[붙여넣기 옵션] → [값만 붙여넣기]**를 하면 변환된 값만 유지할 수 있습니다.
✅ 이 방법의 장점: 기존 데이터를 유지하면서 별도의 날짜 열을 만들 수 있습니다.
[값만 붙여 넣기]를 사용하는 방법은 아래와 같습니다.
- DATE 수식이 반영된 컬럼을 선택하신 뒤 복사(Ctrl + C)
- 원하시는 컬럼을 선택하여 붙여넣기를 하시되 붙여 넣으신 뒤 "붙여넣기 옵션"에서 "123"를 선택하셔서 값으로 붙여넣기를 하시면 됩니다.
간략하게나마 글을 마치겠으며 이 글이 여러분들에게 조금이나마 도움이 되었으면 합니다.
감사합니다.
지나가시는 길에 댓글 하나 적어주시면 더욱 감사할 것 같아요~ 오늘도 행복한 하루 보내시길 바라겠습니다. ^^