엑셀에는 다양한 날짜 형식이 있고 사용자가 직접 날짜서식을 만들어 사용할 수도 있다. 그리고 사용자가 날짜를 입력했을 때 셀에 적용된 서식에 맞게 날짜가 표시된다.
그런데 주의할 점은 엑셀에서 입력 가능한 날짜 형식은 정해져 있다는 것이다.
예를들어 2017-5-22라고 입력하면 엑셀이 인식할 수 있는 날짜 형식이므로 날짜로 인식된다. 하지만 2017.5.22라고 입력하면 날짜로 인식되지 않는다. 날짜로 인식이 안되면 날짜 간에 계산이 안되므로 (경과한 기간 연봉, 호봉 계산 등등) 문제가 생긴다.
따라서 애초에 사용자가 엑셀이 인식할 수 있는 날짜 서식에 맞게 입력하면 문제가 생기지 않을 것이다. 하지만 세상일이 뜻대로 되는 것은 아니다. 내가 잘해도 남이 잘못해 놓으면 고칠 줄도 알아야 한다. 다른 부서로부터 받은 엑셀파일에 입력된 날짜가 모두 2017.5.22와 같이 잘못된 날짜 형식인 경우에 날짜 자동변환이 가능한 형식으로 변경할 필요가 있다.
- 엑셀 날짜 자동변환의 원리
엑셀에서 날짜 자동변환이 가능한 형식을 알아보려면 셀 서식(단축키 Ctrl+1)에 들어가 보면 된다.
그림에서 보이는 엑셀의 기본 날짜 형식에 맞게 날짜를 입력하면 자동으로 날짜 데이터라는 것으로 바뀐다. 날짜 데이터는 1900년 1월 1일을 1로 잡고 여기에서 경과한 일자로 계산된다. (예를 들어 1900-01-010 이라고 입력하면 엑셀은 날짜 데이터 10이 된다.) 따라서 2012년 3월 14일로 입력하든 2012-03-14로 입력하든 간에 날짜 입력 형식이 다르더라도 같은 날짜 데이터인 것이다. 반대로 날짜 데이터는 다양한 표시형식으로 표시될 수 있다. 날짜 데이터와 표시형식(입력형식)의 상호적인 관계를 이해하길 바란다.
(※ 날짜 데이터 값을 알고 싶다면 셀 서식을 일반 또는 숫자로 바꾸면 된다.)
따라서 날짜 데이터는 결국은 양의 정수인 것이다.
문제는 날짜를 입력할 때 엑셀의 기본 날짜 입력 형식에 맞지 않게 입력해 놓은 경우다. 서두에 언급했는데 2017.5.22와 같은 형식은 기본 날짜 형식에 없으므로 날짜로 인식되지 않는다. 날짜 간 계산도 안될 뿐 아니라 날짜 데이터도 아니므로 다른 날짜 형식으로 바꿀 수도 없다.
그렇다고 사용자지정서식을 yyyy.m.dd 와 같이 만들어 적용해 보아도 엑셀이 날짜로 봐주지 않는다. 사용자 지정 서식은 표시형식은 정의 하지만 입력형식을 정의하지는 않기 때문이다.
애초에 날짜 입력 형식이 잘못된 경우의
해결책은 잘못 입력된 날짜 형식을 제대로된 날짜 형식으로 변경해 주면 된다.
- 엑셀 날짜 자동변환1
데이터 형식이 날짜 형식으로 자동변환이 되었다.
- 엑셀 날짜 자동변환2 함수를 사용하는 방법
사실 함수를 사용하는 방법은 위 방법보다는 복잡한데 간간히 긴요하게 쓰인다.(위 방법으로 안되는 경우 함수를 사용해서 해결할 수 있다.) 함수의 쓰임새도 알아둘겸 가볍게 읽어보자.
① 년, 월, 일 사이에 구분자가 없는 형식
구분자가 없는 날짜 형식은 20170522 또는 2017522 와 같은 형식이 될 수 있다. 이런 입력은 날짜가 아닌 숫자로 인식되므로 날짜 데이터로 사용될 수 있다. 그러나 2017052는 2017년 5월 22일을 의미하는 것이 아니라 1900년의 첫 날부터 2017052일 경과한 일자를 의미하므로 날짜형식으로 바꿀 수 있더라도 의미는 없다.
또는 수식에 ="2017052"와 같이 텍스트 형식으로 입력된 경우가 될 수도 있다.
다음은 이를 해결하는 과정이다.
TEXT(B12, "0000-00-00")
해설 : B12 셀의 숫자를 서식코드"0000-00-00"을 적용하여 텍스트로 반환한다.
참고 : 엑셀 사용자지정서식 숫자, 텍스트, 색, 날짜 지정
2017-05-22 는 날짜 표시 형식이지만 현재 텍스트 형식 데이터이므로 숫자로 변경시켜야 한다.
--연산을 해서 숫자 형식으로 바꿔준다.
--TEXT(B12, "0000-00-00")
해설 :
--는 -1을 두 번 곱하는 것이다. (또는 1을 곱해줘도 된다.)
이것은 텍스트에 숫자연산을 하면 텍스트가 숫자로 변환되는 원리를 이용한 것이다.
2017-05-22 라는 텍스트를 숫자로 변환시키면 자동으로 날짜 형식으로 인식되어 날짜 데이터로 변환된다. 그래서 42877이라는 수가 나온 것.
이제 나머지 셀들에 대해 자동채움을 해 준다.
그리고 날짜 데이터로 변환된 셀들을 모두 복사해준다.
복사한 내용을 원래의 셀에 붙여넣기를 해준다. 이 때 그냥 붙여넣지 말고 반드시 선택하여 붙여넣기에서 값만 붙여넣기를 해준다.
값을 붙여 넣은 후 셀 서식에서 날짜 형식을 지정해 주고 옆에 파란 테두리의 내용은 삭제해준다.
날짜 자동변환이 되었다.
② 지원하지 않는 날짜 구분자를 사용한 경우
날짜 구분자를 .으로 사용한 경우는 흔하다. 그런데 엑셀에서는 2017.5.22와 같은 표시 형식은 날짜로 인식되지 않는다.
날짜 자동변환이 가능하려면 .을 -로 변경하고 숫자형식으로 만들면 된다.
다음은 이를 해결하는 과정인데 생략된 부분은 부분은 앞의 내용과 겹친다.
SUBSTITUTE 함수를 사용하면 된다.
SUBSTITUTE(B12, ".", "-")
해설 : B12셀의 텍스트의 내용 중에서 . 을 -로 바꾼다.
바뀐 결과 2017-5-22는 엑셀에서 인식 가능한 형식이지만 텍스트 데이터 형식이므로 숫자 데이터 형식으로 변경해야 한다. ①을 해결할 때 처럼
--를 곱해주면 되는데 이 후의 과정은 ① 년, 월, 일 사이에 구분자가 없는 형식 의 과정과 동일하므로 생략한다.
정리하면 엑셀 날짜 자동변환이 가능하도록 하려면 날짜 입력 형식에 주의해야 한다. 만약 잘못된 날짜형식으로 입력된 경우라면 엑셀이 인식가능한 형식으로 변경해주는 작업이 필요하다.