Excel

แปลงข้อความเป็นวันที่

Convert Text Date

สูตร Excel: แปลงข้อความเป็นวันที่สูตรทั่วไป |_+_| สรุป

ในการแปลงข้อความในรูปแบบวันที่ที่ไม่รู้จักให้เป็นวันที่ใน Excel ที่เหมาะสม คุณสามารถแยกวิเคราะห์ข้อความและประกอบวันที่ที่เหมาะสมด้วยสูตรตามฟังก์ชันต่างๆ ได้แก่ DATE, LEFT, MID และ RIGHT ในตัวอย่างที่แสดง สูตรใน C6 คือ:





= DATE ( LEFT (text,4), MID (text,5,2), RIGHT (text,2))

สูตรนี้แยกค่าปี เดือน และวันออกจากกัน และใช้ฟังก์ชัน DATE เพื่อรวมค่าเหล่านั้นเป็นวันที่ 24 ตุลาคม 2000

หมายเหตุ: ก่อนที่คุณจะใช้สูตร โปรดดูวิธีอื่นในการแปลงข้อความเป็นวันที่ด้านล่าง

พื้นหลัง

เมื่อคุณทำงานกับข้อมูลจากระบบอื่น คุณอาจพบสถานการณ์ที่ Excel ไม่รู้จักวันที่อย่างถูกต้อง ซึ่งจะถือว่าวันที่เป็นข้อความแทน ตัวอย่างเช่น คุณอาจมีค่าข้อความดังนี้:





ข้อความ วันที่เป็นตัวแทน
20001024 24 ตุลาคม 2000
20050701 1 กรกฎาคม 2554
19980424 24 เมษายน 1998
02.28.2014 28 กุมภาพันธ์ 2014

เมื่อ Excel ประเมินค่าวันที่เป็นข้อความ ทางเลือกหนึ่งคือการใช้สูตรเพื่อแยกวิเคราะห์ข้อความเป็นส่วนประกอบ (ปี เดือน วัน) และใช้สูตรเหล่านี้สร้างวันที่ด้วยฟังก์ชัน DATE ดังที่กล่าวไว้ข้างต้น เราขอแนะนำให้คุณลองใช้วิธีแก้ปัญหาด้านล่างก่อน (เพิ่มศูนย์และใช้ข้อความในคอลัมน์) ก่อนที่คุณจะใช้สูตร วิธีแก้ปัญหาทั้งสองนี้เร็วกว่าและต้องใช้ความพยายามน้อยลง

คำอธิบาย

NS ฟังก์ชัน DATE สร้างวันที่ที่ถูกต้องโดยใช้อาร์กิวเมนต์สามตัว: ปี เดือน และวัน:



 
= DATE ( LEFT (B6,4), MID (B6,5,2), RIGHT (B6,2))

ในเซลล์ C6 เราใช้ฟังก์ชัน LEFT, MID และ RIGHT เพื่อแยกส่วนประกอบเหล่านี้ออกจากสตริงข้อความ และป้อนผลลัพธ์ลงในฟังก์ชัน DATE:

excel ตารางเดือยคืออะไร
 
= DATE (year,month,day)

NS ฟังก์ชัน LEFT แยกอักขระ 4 ตัวซ้ายสุดสำหรับปี the ฟังก์ชั่น MID แยกอักขระในตำแหน่ง 5-6 ของเดือนและ ฟังก์ชันขวา แยกอักขระ 2 ตัวขวาสุดตามวัน ผลลัพธ์แต่ละรายการจะถูกส่งกลับไปยังฟังก์ชัน DATE โดยตรง ผลลัพธ์สุดท้ายคือวันที่ใน Excel ที่เหมาะสมซึ่งสามารถจัดรูปแบบได้ตามต้องการ

วิธีนี้สามารถปรับแต่งได้ตามต้องการ ตัวอย่างเช่น รูปแบบวันที่ที่ไม่รู้จักในแถวที่ 8 คือ dd.mm.yyyy และสูตรใน C8 คือ:

 
= DATE ( LEFT (B6,4), MID (B6,5,2), RIGHT (B6,2))

ข้อความรูปแบบยาว

บางครั้ง คุณอาจมีวันที่ในรูปแบบที่ยาวกว่า เช่น '11 เมษายน 2020 08:43:13 น.' ซึ่ง Excel ไม่รู้จักอย่างถูกต้อง ในกรณีนี้ คุณอาจสามารถปรับสตริงในลักษณะที่ทำให้ Excel รู้จักวันที่ด้วย . ได้อย่างถูกต้อง ฟังก์ชัน SUBSTITUTE . สูตรด้านล่างแทนที่อินสแตนซ์ที่สองของช่องว่าง (' ') ด้วยเครื่องหมายจุลภาคและช่องว่าง (', '):

 
= DATE ( RIGHT (B8,4), MID (B8,4,2), LEFT (B8,2))

เมื่อเราเพิ่มเครื่องหมายจุลภาคหลังชื่อเดือน Excel จะเข้าใจวันที่ แต่ก็ยังต้องการ 'เตะ' อีกเล็กน้อย นั่นเป็นเหตุผลที่เราเพิ่มศูนย์ในตอนท้าย การดำเนินการทางคณิตศาสตร์ทำให้ Excel พยายามแปลงสตริงเป็นตัวเลข เมื่อสำเร็จ จะส่งผลให้วันที่ใน Excel ถูกต้อง หมายเหตุคุณอาจต้องสมัครวันที่ การจัดรูปแบบตัวเลข เพื่อแสดงวันที่อย่างถูกต้อง

ไม่มีสูตร

ก่อนที่คุณจะใช้สูตรเพื่อแยกวิเคราะห์และสร้างวันที่จากข้อความด้วยตนเอง ให้ลองแก้ไขด้วยวิธีใดวิธีหนึ่งด้านล่างนี้ ตัวเลือกแรกใช้การดำเนินการทางคณิตศาสตร์เพื่อ 'เขยิบ' Excel เล็กน้อย และบังคับให้พยายามประเมินข้อความเป็นตัวเลข เพราะ วันที่ใน Excel เป็นตัวเลขจริง ซึ่งมักจะทำเคล็ดลับได้ คุณอาจต้องใช้รูปแบบวันที่หากการดำเนินการสำเร็จ

เพิ่มศูนย์เพื่อแก้ไขวันที่

บางครั้ง คุณจะพบวันที่ในรูปแบบข้อความที่ Excel ควรรู้จัก ในกรณีนี้ คุณอาจบังคับให้ Excel แปลงค่าข้อความเป็นวันที่ได้โดยการเพิ่มศูนย์ให้กับค่า เมื่อคุณบวกศูนย์ Excel จะพยายามบังคับค่าข้อความให้เป็นตัวเลข เนื่องจากวันที่เป็นเพียงตัวเลข เคล็ดลับนี้จึงเป็นวิธีที่ดีในการแปลงวันที่ในรูปแบบข้อความที่ Excel ควรเข้าใจจริงๆ

วิธีดึงเวลาจากวันที่และเวลาใน excel

หากต้องการแปลงวันที่โดยการเพิ่มศูนย์ ให้ลอง วางแบบพิเศษ :

วิธีสร้างเมนูแบบเลื่อนลงใน excel 2010
  1. ป้อนศูนย์ (0) ในเซลล์ที่ไม่ได้ใช้และคัดลอกไปยังคลิปบอร์ด
  2. เลือกวันที่มีปัญหา
  3. วางแบบพิเศษ > ค่า > เพิ่ม
  4. ใช้รูปแบบวันที่ (ถ้าจำเป็น)

คุณยังสามารถเพิ่มศูนย์ในสูตรดังนี้:

 
= SUBSTITUTE (A2,' ',', ',2)+0 // add comma after month

โดยที่ A1 มีวันที่ไม่รู้จัก

ข้อความเป็นคอลัมน์เพื่อแก้ไขวันที่

อีกวิธีหนึ่งในการให้ Excel รู้จักวันที่คือการใช้ ข้อความเป็นคอลัมน์ ลักษณะเฉพาะ:

เลือกคอลัมน์ของวันที่ จากนั้นลองใช้ Data > Text to columns > Fixed > Finish

ถ้า Excel รู้จักวันที่ จะแก้ไขทั้งหมดในขั้นตอนเดียว

ผู้เขียน Dave Bruns


^