Excel에서 다양한 텍스트 항목을 날짜로 변환
Excel에서 텍스트를 날짜로 변환하기 위해 StackExchange에서 여러 가지 제안 된 솔루션을 시도했지만 내가 가진 데이터에 대해 안정적으로 작동하지 않습니다. 아래에서 설명을 참조하십시오.
"시작일"이있는 열이 있습니다. 다음 형식의 날짜를 포함합니다.
(시작 상태) 9/17/2019 9:30:59 AM 10/20/2020 4:45:59 PM 2/26/2020 12:20:59 PM
전체 항목을 Excel 날짜 값으로 변환하여 유용한 계산을 수행 할 수 있습니다. 예를 들어 2020 년 11 월 21 일의 종료 시간에서 2020 년 11 월 20 일의 시작 시간을 뺍니다. 위의 항목은 (바람직하게는) 23/02/2020 20:31:59 (나는 영국인이기 때문에)로 끝나지 만 미국 버전으로 가져올 수 있어도 괜찮습니다.
(원하는 종료 상태) 17/09/2019 09:30 20/10/2020 16:45 26/02/2020 12:20
이 문제를 해결하는 것으로 보이는 다른 답변을 지적하지 마십시오. 나는 그들을 사용해 보았습니다. 각각에 문제가있는 것으로 보이며이 문제를 해결하는 방법을 볼 수 없거나이 질문을 게시하지 않았을 것입니다.
예를 들어 시도 =datevalue(left(a1,9))하면 두 자릿수 월 (예 : 10, 11, 12)이있는 항목을 얻지 못합니다.
=date(mid.... etc똑같은 방법을 시도하면 다른 셀의 정보 길이가 다르기 때문에 모든 정보를 안정적으로 확보하지 못합니다.
날짜 / 시간 형식이 아닌 셀로 분할하고 PM 또는 AM을 삭제할 수있는 다른 열로 잘라내 는 Text-To-Columns 방법 을 사용했지만 이는 20:31:59 대신에 한 열에는 08:31:59, 다른 열에는 PM이 있습니다. 20:31:59를 반환하는 방법을 알 수 없습니다.
이 질문의 내용이 명확하지 않은 경우 질문하십시오.
정말 분명한 것을 놓친 경우 사과드립니다.하지만이 문제를 해결하기 위해 몇 시간을 보냈습니다.
답변
내 첫 번째 시도는 텍스트 셀에서 사용자 지정 날짜 형식을 시도하고 일치시키는 것입니다. 작동하면 셀의 텍스트가 한 칸 들여 쓰기되고 = A1 + 0이라고 말하여 오류없이 날짜에 0 일을 추가 할 수 있습니다. 그런 다음 원하는대로 새 셀을 다시 포맷합니다.
최악의 경우 문자열을 잘 이해하는 네이티브 날짜 형식으로자를 수 있습니다. 고정 된 길이는 필요하지 않지만 공백 및 / 문자를 구분 기호로 사용할 수 있습니다 (예 : 두 / 사이의 날짜를 가져 오기 위해)
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
의미 상 의미
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
+1 또는 -1을 주거나받습니다. 나는 지금 공식을 테스트하기 위해 내 컴퓨터 앞에 있지 않습니다. 그러나 예를 들어 한 문자를 반환하면 길이 인수에 +1을 추가합니다. 일 월과 연도 (필요한 경우 시간)를 분리하면 date 함수로 날짜를 재구성 할 수 있습니다.
좋아, 그래서 나는 당신에게 적절한 해결책이 있다고 생각합니다. Excel에서 텍스트를 날짜로 변환하는 방식은 Windows 지역 설정에 따라 달라집니다. Excel에서 셀의 날짜 형식을 설정하면 날짜 값이 표시되는 방식 만 변경 되며 처음에 변환 된 방식 은 변경 되지 않습니다.
Windows의 내 지역 설정은 현재 남아프리카 공화국으로 설정되어 있습니다. 내 스프레드 시트는 인접한 수식 = A1 + 0으로이 문제를 해결합니다.
이제 Windows 제어판 > 날짜, 시간 또는 숫자 형식 변경으로 이동하여 형식을 일시적으로 영어 (미국)로 변경 한 다음 스프레드 시트로 돌아가 공식을 다시 평가합니다 (만약 ctrl + alt + F9를 누르십시오). 자동 업데이트되지 않음) :
이제 새 날짜 값 열을 복사하여 값으로 붙여 넣습니다. 파일을 저장하십시오. 지역 설정을 원래대로 변경하십시오. 열을 선택하고 "사용자 정의"에서 원하는 날짜 및 시간 셀 형식을 저장합니다.
그래서:
