엑셀 날짜 자동변환 잘못 입력된 날짜 형식 해결 방법

엑셀에는 다양한 날짜 형식이 있고 사용자가 직접 날짜서식을 만들어 사용할 수도 있다. 그리고 사용자가 날짜를 입력했을 때 셀에 적용된 서식에 맞게 날짜가 표시된다.


그런데 주의할 점은 엑셀에서 입력 가능한 날짜 형식은 정해져 있다는 것이다. 

예를들어 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는 엑셀에서 인식 가능한 형식이지만 텍스트 데이터 형식이므로 숫자 데이터 형식으로 변경해야 한다. ①을 해결할 때 처럼 

--를 곱해주면 되는데 이 후의 과정은 ① 년, 월, 일 사이에 구분자가 없는 형식 의 과정과 동일하므로 생략한다.


정리하면 엑셀 날짜 자동변환이 가능하도록 하려면 날짜 입력 형식에 주의해야 한다. 만약 잘못된 날짜형식으로 입력된 경우라면 엑셀이 인식가능한 형식으로 변경해주는 작업이 필요하다.

이 댓글을 비밀 댓글로
    • 포듣
    • 2017.08.08 10:44
    정말 천재이십니다. 많은 도움이 되었습니다. 즐찾하고 자주 찾아뵙겠습니다.
    • DAHYE
    • 2017.08.22 16:50
    너무 너무 훌륭한 글입니다!!!!!
    그런데 =--SUBSTITUTE(A1,"/","-";) 로 해서 숫자로 변경하고 싶은데 VALUE에러가뜹니다. '--' 의 역할이 숫자로 바꿔주는 것이라 하셨는데 밸류에러가 뜨는 건 어떻게 해결해야할까요ㅠㅠ:
  1. 올려주신 포스팅 자료 덕분에 긴급한 업무 수월하게 해결했습니다.

    고맙습니다.
    • 감사감사
    • 2017.12.12 16:02
    와우 정말 감사합니다~
    당신은 멋쟁이!!!
    • sylvia
    • 2018.01.21 15:39
    감사합니다.^^
    • 자영
    • 2018.05.12 17:16
    아 정말 감사합니다 !! 급한 업무 잘 해결했습니다.
    • 나라
    • 2018.07.26 14:35
    다른 포스팅 도움안되는 방법 써있던데 ...덕분에 해결되어 속이 시원해요, 감사합니다.
    • YDG
    • 2018.08.16 14:59
    감사합니다.
    • 채수진
    • 2018.09.04 11:15
    대박감사해요 정말유용한글이였습니다 ㅠ
    • 연양갱
    • 2021.02.26 18:51
    함수를 넣어서 해봤는데 인수가 많다는 내용의 오류가 뜨네요. text 함수 나오는 부분부터 이해가 잘 안 되는데.. 혹시 설명해주실 수 있나요?
    • 지혜
    • 2021.04.09 11:57
    엑셀 잘 몰라서 버벅 되였는데.. 한가지 여줘봅니다.
    2021년 4월 9일 로 입력하면 어떤 파일에서는 보기에 2021-4-9로 나오고 셀서식에서 날자와 사용자지정을 했는데도 어떤 파일에는 2021년 4월 9일로 나오는데 어디가 잘못인지 ...
    그리고 파일을 올리는 기능은 없나요?

    • 안녕하세요. 셀을 정확히 지정하시고 서식을 적용했는지 확인해보세요.
      많은 도움 못드려서 죄송합니다.