ในการแปลงข้อความในรูปแบบวันที่ที่ไม่รู้จักให้เป็นวันที่ใน 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
- ป้อนศูนย์ (0) ในเซลล์ที่ไม่ได้ใช้และคัดลอกไปยังคลิปบอร์ด
- เลือกวันที่มีปัญหา
- วางแบบพิเศษ > ค่า > เพิ่ม
- ใช้รูปแบบวันที่ (ถ้าจำเป็น)
คุณยังสามารถเพิ่มศูนย์ในสูตรดังนี้:
= SUBSTITUTE (A2,' ',', ',2)+0 // add comma after month
โดยที่ A1 มีวันที่ไม่รู้จัก
ข้อความเป็นคอลัมน์เพื่อแก้ไขวันที่
อีกวิธีหนึ่งในการให้ Excel รู้จักวันที่คือการใช้ ข้อความเป็นคอลัมน์ ลักษณะเฉพาะ:
เลือกคอลัมน์ของวันที่ จากนั้นลองใช้ Data > Text to columns > Fixed > Finish
ถ้า Excel รู้จักวันที่ จะแก้ไขทั้งหมดในขั้นตอนเดียว
ผู้เขียน Dave Bruns