엑셀에서 자료를 관리하다보면 날짜 데이터의 필터, 정렬이 먹히지 않아 난감할 때가 있습니다. 특히 은행 통장 내역을 불러왔을 때, 날짜가 문자로 저장되어 필터가 안 되는 문제는 많은 직장인들이 겪는 대표적인 문제인데요.
함수 2개만 사용해서 문자로된 날짜에서 날짜와 시간을 추출할 수 있습니다!😊
하지만 이제 더 이상 고민할 필요 없습니다! LEFT 함수 + SUBSTITUTE 함수 2개만 기억하세요! 이 두 가지 함수만 사용하면 엑셀 날짜 필터 오류를 단 10초 만에 깔끔하게 해결할 수 있습니다! 🚀
- 날짜 추출하기 : 날짜데이터 오른쪽에 비어있는 열을 추가한 후, LEFT 함수로 '날짜'를 추출합니다. 예를 들어, 아래 날짜에서는 첫 10글자를 추출해야 하므로, 다음과 같이 LEFT 함수를 작성합니다.
예) "2024.12.31 15:31:00" → =LEFT(날짜,10) → "2024.12.31"
'문장의 첫 10글자를 추출합니다.
LEFT 함수로 날짜값을 추출합니다.
- 엑셀은 1900년 1월 1일부터 1씩 증가하는 숫자로 날짜를 관리합니다. 따라서, 추출한 날짜를 숫자로 변경해야 하는데요. 엑셀은 마침표(.)로 구분된 날짜는 숫자로 인식하지 못하기 때문에, 만약 날짜가 마침표로 구분되어 있다면 대시(-) 기호로 변경해야 합니다. 이를 위해, 아래와 같이 SUBSTITUTE 함수를 사용해 마침표(.)를 대시(-) 로 변경합니다.
=SUBSTITUTE("2024.12.31",".","-") → "2024-12-31"
'문장에서 마침표(.)를 대시(-)로 변경합니다.
SUBSTITUTE 함수를 사용해 마침표(.)를 대시(-)로 변경합니다.
- 이제 문자 형식의 날짜에 1을 곱하면 날짜 일련번호(숫자)가 계산됩니다.
=SUBSTITUTE("2024.12.31",".","-")*1 → 45657
'1을 곱해서 숫자로 변환합니다.
1을 곱하면 날짜 일련번호가 계산됩니다.
- [홈] 탭 - [표시형식] 에서 날짜 서식을 선택하거나 Ctrl + Shift + 3 으로 날짜 서식을 변경한 후, 수식을 자동 채우기하면 날짜 데이터 추출이 완료됩니다.
표시 형식을 날짜 서식으로 변경합니다.
- 시간 추출하기 : 동일한 원리로 시간을 추출할 수 있습니다. 다음과 같이 RIGHT 함수를 작성하면 문자 형식의 날짜+시간 데이터에서 시간이 추출됩니다.
"2024.12.31 15:31:00" → =RIGHT(셀,8)*1
'문장의 끝에서 8자리를 추출한 후, 숫자(시간 데이터)로 변환합니다.
RIGHT 함수를 사용해 시간 값을 추출합니다.
- 시간을 추출한 범위를 선택한 후, [홈] - [표시형식] 에서 시간 서식을 적용합니다.
표시 형식을 시간 서식으로 변경합니다.
- 요일 표시하기 : 셀 서식을 활용하면 요일에 날짜를 표시할 수 있습니다. 날짜 범위를 선택한 후, 셀 서식에서 아래와 같이 표시형식을 변경하면 날짜와 요일이 함께 표시됩니다.
yyyy-mm-dd (aaa)
예) 2024-12-31(화), 2025-01-01 (수)
'aaa 는 짧은 요일(월,화...), aaaa는 긴 요일(월요일, 화요일 ...) 을 표시합니다.
셀 서식으로 날짜와 요일을 함께 표시할 수 있습니다.
오빠두Tip : 실무에서 꼭 필요한 셀 서식 기초 이론과 핵심 표시 형식은 아래 5분 기초 입문 강의에서 알기 쉽게 정리했으니 참고하세요!😊
- 텍스트 나누기 활용 : 텍스트 나누기를 활용하면 날짜 + 시간데이터를 빠르게 구분할 수 있습니다. 날짜가 입력된 범위를 선택한 후, 아래 단축키를 하나씩 차례대로 입력합니다.
(꿀팁을 공유해주신 4riana6rande님께 감사드립니다!)
Alt - A - E - D - N - S - N - D - F
(데이터 - 텍스트나누기 - 구분기호(공백) - 날짜 출력)
텍스트나누기를 사용하면 날짜+시간을 더욱 간편하게 구분할 수 있습니다.
오빠두Tip : 단, 텍스트 나누기는 원본 데이터를 직접 변환하므로 주의해서 사용하세요!😊
