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

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

เมื่อคุณนำเข้าข้อมูลลงใน Excel อาจมีหลายรูปแบบที่ Excel ไม่รู้จัก บางครั้งเมื่อนำเข้าวันที่จากแหล่งภายนอก วันที่จะถูกจัดรูปแบบเป็นข้อความ

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

หากคุณมีปัญหานี้ มีหลายวิธีที่คุณสามารถใช้แปลงข้อความเป็นวันที่ใน Excel และเราจะกล่าวถึงทั้งหมดในบทช่วยสอนนี้

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

ถ้าเวิร์กชีตของคุณมีวันที่ที่จัดรูปแบบเป็นข้อความ แทนที่จะเป็นวันที่จริง คุณจะไม่สามารถใช้ในการคำนวณใดๆ ได้ ดังนั้น คุณต้องแปลงกลับเป็นวันที่จริง

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

คุณสามารถแปลงข้อความเป็นวันที่ใน Excel ได้หลายวิธี ได้แก่

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

แปลงข้อความเป็นวันที่โดยใช้ตัวเลือกการตรวจสอบข้อผิดพลาด

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

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

หากเซลล์ของคุณแสดงตัวบ่งชี้ข้อผิดพลาดนั้น ให้คลิกที่เครื่องหมายอัศเจรีย์ จากนั้นระบบจะแสดงตัวเลือกสองสามตัวเลือกในการแปลงวันที่ที่จัดรูปแบบเป็นข้อความให้เป็นวันที่จริง Excel จะแสดงตัวเลือกในการแปลงเป็น 19XX หรือ 20XX (19XX สำหรับ 1915, 20XX สำหรับ 2015) เลือกตัวเลือกที่เหมาะสม

จากนั้นข้อความจะถูกแปลงเป็นรูปแบบวันที่ที่เหมาะสม

วิธีเปิดใช้งานตัวเลือกการตรวจสอบข้อผิดพลาดใน Excel

โดยปกติ ตัวเลือกการตรวจสอบข้อผิดพลาดจะเปิดอยู่ใน Excel ตามค่าเริ่มต้น ถ้าฟีเจอร์การตรวจสอบข้อผิดพลาดใช้ไม่ได้กับคุณ คุณต้องเปิดใช้งานการตรวจสอบข้อผิดพลาดใน Excel

ในการดำเนินการนี้ ให้คลิกที่แท็บ "ไฟล์" และเลือก "ตัวเลือก" ในแผงด้านซ้าย

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

แปลงข้อความเป็นวันที่โดยใช้คุณสมบัติข้อความ Excel เป็นคอลัมน์

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

การแปลงสตริงข้อความธรรมดาเป็นวันที่

สมมติว่าวันที่ของคุณมีรูปแบบเป็นสตริงข้อความดังนี้:

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

ขั้นแรก เลือกช่วงของรายการข้อความที่คุณต้องการแปลงเป็นวันที่ จากนั้นไปที่แท็บ 'ข้อมูล' ใน Ribbon แล้วคลิกตัวเลือก 'ข้อความเป็นคอลัมน์' ในกลุ่มเครื่องมือข้อมูล

ตัวช่วยสร้างข้อความเป็นคอลัมน์จะปรากฏขึ้น ในขั้นตอนที่ 1 ของวิซาร์ดข้อความเป็นคอลัมน์ ให้เลือกตัวเลือก "ตัวคั่น" ใต้ประเภทข้อมูลดั้งเดิมแล้วคลิก "ถัดไป"

ในขั้นตอนที่ 2 ให้ยกเลิกการเลือกช่อง "ตัวคั่น" ทั้งหมดแล้วคลิกถัดไป

ในขั้นตอนสุดท้ายของตัวช่วยสร้าง ให้เลือก 'วันที่' ภายใต้รูปแบบข้อมูลคอลัมน์ และเลือกรูปแบบวันที่ของคุณจากรายการแบบเลื่อนลงถัดจาก 'วันที่' แล้วคลิกปุ่ม 'เสร็จสิ้น' ในกรณีของเรา เรากำลังแปลงวันที่ของข้อความที่แสดงเป็น “01 02 1995” (วัน เดือน ปี) ดังนั้นเราจึงเลือก 'DMY' จากรายการดรอปดาวน์ 'วันที่:'

ตอนนี้ Excel จะแปลงวันที่ที่เป็นข้อความของคุณเป็นวันที่จริง และแสดงวันที่เหล่านั้นชิดขวาในเซลล์

บันทึก: ก่อนที่คุณจะเริ่มใช้คุณลักษณะ Text to Column ตรวจสอบให้แน่ใจว่าสตริงข้อความทั้งหมดของคุณอยู่ในรูปแบบที่เหมือนกัน มิฉะนั้น ข้อความจะไม่ถูกแปลง ตัวอย่างเช่น หากวันที่ของข้อความบางรูปแบบมีรูปแบบเหมือนเดือน/วัน/ปี (MDY) ในขณะที่วันที่อื่นๆ เป็นวัน/เดือน/ปี (DMY) และเมื่อคุณเลือก "DMY" ในขั้นตอนที่ 3 คุณจะได้รับผลลัพธ์ที่ไม่ถูกต้อง ดังแสดงในภาพด้านล่าง

การแปลงสตริงข้อความที่ซับซ้อนเป็นวันที่

คุณลักษณะข้อความเป็นคอลัมน์มีประโยชน์เมื่อคุณต้องการแปลงสตริงข้อความที่ซับซ้อนเป็นวันที่ ช่วยให้คุณใช้ตัวคั่นเพื่อระบุว่าข้อมูลของคุณควรแยกและแสดงที่ใดใน 2 คอลัมน์ขึ้นไป และรวมส่วนที่แยกของวันที่เป็นวันที่ทั้งหมดโดยใช้ฟังก์ชัน DATE

ตัวอย่างเช่น หากวันที่ของคุณแสดงเป็นสตริงข้อความแบบหลายส่วน ดังนี้:

วันพุธที่ 01 กุมภาพันธ์ 2020

01 กุมภาพันธ์ 2020 16.10 น.

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

ขั้นแรก เลือกสตริงข้อความทั้งหมดที่คุณต้องการแปลงเป็นวันที่ คลิกปุ่ม 'ข้อความเป็นคอลัมน์' บนแท็บ 'ข้อมูล' ในขั้นตอนที่ 1 ของวิซาร์ดข้อความเป็นคอลัมน์ ให้เลือกตัวเลือก "ตัวคั่น" ใต้ประเภทข้อมูลดั้งเดิมแล้วคลิก "ถัดไป"

ในขั้นตอนที่ 2 ของวิซาร์ด ให้เลือกตัวคั่นที่สตริงข้อความของคุณมี และคลิก 'ถัดไป' สตริงข้อความตัวอย่างของเราคั่นด้วยเครื่องหมายจุลภาคและช่องว่าง – “Monday, February 01, 2015, 13:00 PM” เราควรเลือก 'จุลภาค' และ 'ช่องว่าง' เป็นตัวคั่นเพื่อแยกสตริงข้อความออกเป็นหลายคอลัมน์

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

ตอนนี้ ส่วนของวันที่ (วันในสัปดาห์ เดือน ปี เวลา) ถูกแบ่งออกเป็นคอลัมน์ B, C, D, E, F และ G

จากนั้นรวมส่วนของวันที่เข้าด้วยกันโดยใช้สูตร DATE เพื่อให้ได้วันที่ทั้งหมด

ไวยากรณ์ของฟังก์ชัน Excel DATE:

=DATE(ปี เดือน วัน)

ในตัวอย่างของเรา ส่วนเดือน วัน และปีอยู่ในคอลัมน์ C, D และ E ตามลำดับ

ฟังก์ชัน DATE จะจดจำเฉพาะตัวเลขเท่านั้น ไม่ใช่ข้อความ เนื่องจากค่าเดือนในคอลัมน์ C เป็นสตริงข้อความทั้งหมด เราจึงต้องแปลงเป็นตัวเลข ในการทำเช่นนั้น คุณต้องใช้ฟังก์ชัน MONTH เพื่อเปลี่ยนชื่อของเดือนเป็นจำนวนเดือน

ในการแปลงชื่อเดือนเป็นตัวเลขของเดือน ให้ใช้ฟังก์ชัน MONTH ภายในฟังก์ชัน DATE:

=เดือน(1&C1)

ฟังก์ชัน MONTH จะเพิ่ม 1 ลงในเซลล์ C2 ซึ่งมีชื่อของเดือนเพื่อแปลงชื่อเดือนเป็นหมายเลขเดือนที่เกี่ยวข้อง

นี่คือฟังก์ชัน DATE ที่เราต้องใช้เพื่อรวมส่วนของวันที่จากคอลัมน์ต่างๆ:

=วันที่(E1,เดือน(1&C1),D1)

ตอนนี้ใช้จุดจับเติมที่มุมด้านล่างของเซลล์สูตร แล้วนำสูตรไปใช้กับคอลัมน์

แปลงข้อความเป็นวันที่โดยใช้วิธีค้นหาและแทนที่

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

ในการแก้ไขปัญหานี้ ให้ใช้คุณสมบัติค้นหาและแทนที่ ด้วยการเปลี่ยนตัวคั่นช่วงเวลาที่ไม่เป็นมาตรฐาน (.) ด้วยเครื่องหมายทับ (/) หรือขีดกลาง (-) Excel จะเปลี่ยนค่าเป็นวันที่โดยอัตโนมัติ

ขั้นแรก เลือกวันที่ของข้อความทั้งหมดที่คุณต้องการแปลงเป็นวันที่ บนแท็บ "หน้าแรก" ให้คลิกปุ่ม "ค้นหาและเลือก" ที่มุมขวาสุดของ Ribbon แล้วเลือก "แทนที่" หรือกด Ctrl+H เพื่อเปิดกล่องโต้ตอบค้นหาและแทนที่

ในกล่องโต้ตอบ ค้นหาและแทนที่ ให้พิมพ์ตัวคั่นที่ข้อความของคุณมี (ในกรณีของเรา ตัวหยุดเต็ม (.) ในช่อง 'ค้นหาอะไร' และเครื่องหมายทับ (/) หรือขีดกลาง (-) ในช่อง 'แทนที่ด้วย' คลิกปุ่ม 'แทนที่ทั้งหมด' เพื่อแทนที่ตัวคั่น และคลิก 'ปิด' เพื่อปิดหน้าต่าง

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

แปลงข้อความเป็นวันที่โดยใช้เครื่องมือพิเศษวาง

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

ขั้นแรก เลือกเซลล์ว่างแล้วคัดลอก (เลือกแล้วกด Ctrl + C เพื่อคัดลอก)

จากนั้นเลือกเซลล์ที่มีข้อความวันที่ที่คุณต้องการแปลง คลิกขวาแล้วเลือกตัวเลือก 'วางแบบพิเศษ'

ในกล่องโต้ตอบการวางแบบพิเศษ เลือก 'ทั้งหมด' ใต้ส่วนการวาง เลือก 'เพิ่ม' ใต้ส่วนการทำงาน แล้วคลิก 'ตกลง'

คุณยังสามารถดำเนินการคำนวณอื่นๆ ลบ/คูณ/หารค่าในเซลล์ปลายทางด้วยค่าที่วาง (เช่น คูณเซลล์ด้วย 1 หรือหารด้วย 1 หรือลบศูนย์)

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

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

อย่างที่คุณเห็นตอนนี้ ตัวเลขถูกจัดรูปแบบเป็นวันที่และจัดชิดขวา

แปลงข้อความเป็นวันที่โดยใช้สูตร

มีสองฟังก์ชันที่ใช้เป็นหลักในการแปลงข้อความเป็นวันที่: DATEVALUE และ VALUE

การใช้ฟังก์ชัน Excel DATEVALUE

ฟังก์ชัน Excel DATEVALUE เป็นวิธีที่ง่ายที่สุดวิธีหนึ่งในการแปลงวันที่ที่แสดงเป็นข้อความให้เป็นหมายเลขลำดับประจำสินค้าของวันที่

ไวยากรณ์ของฟังก์ชัน DATEVALUE:

=DATEVALUE(วันที่_ข้อความ)

การโต้เถียง: date_text ระบุสตริงข้อความที่คุณต้องการปกปิดหรืออ้างอิงไปยังเซลล์ที่มีข้อความวันที่

สูตร:

=DATEVALUE(A1)

รูปภาพต่อไปนี้แสดงให้เห็นว่าฟังก์ชัน DATEVALUE จัดการกับรูปแบบวันที่ที่แตกต่างกันสองสามรูปแบบซึ่งจัดเก็บเป็นข้อความอย่างไร

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

ตอนนี้คุณมีวันที่จัดรูปแบบในคอลัมน์ C

แม้ว่าจะไม่มีส่วนของปีในวันที่ข้อความของคุณ (A8) DATEVALUE จะใช้ปีปัจจุบันจากนาฬิกาในคอมพิวเตอร์ของคุณ

ฟังก์ชัน DATEVALUE จะแปลงเฉพาะค่าข้อความที่ดูเหมือนวันที่ ไม่สามารถแปลงข้อความที่คล้ายกับตัวเลขเป็นวันที่ และไม่สามารถเปลี่ยนค่าตัวเลขเป็นวันที่ได้ คุณจะต้องใช้ฟังก์ชัน VALUE ของ Excel

การใช้ฟังก์ชัน Excel VALUE

ฟังก์ชัน Excel VALUE สามารถแปลงสตริงข้อความที่คล้ายกับวันที่หรือตัวเลขให้เป็นค่าตัวเลขได้ ดังนั้นจึงมีประโยชน์มากในการแปลงตัวเลขใดๆ ไม่ใช่แค่วันที่

ฟังก์ชัน VALUE:

=VALUE(ข้อความ)

ข้อความ– สตริงข้อความที่เราต้องการแปลงหรืออ้างอิงไปยังเซลล์ที่มีสตริงข้อความ

ตัวอย่างสูตรการแปลงวันที่ข้อความ:

=มูลค่า(A1)

สูตร VALUE ด้านล่างสามารถเปลี่ยนสตริงข้อความที่ดูเหมือนวันที่เป็นตัวเลขดังที่แสดงด้านล่าง

อย่างไรก็ตาม ฟังก์ชัน VALUE ไม่สนับสนุนค่าวันที่ทุกประเภท ตัวอย่างเช่น หากวันที่ใช้ตำแหน่งทศนิยม (A11) จะคืนค่า #VALUE! ข้อผิดพลาด.

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

เพียงเท่านี้ นี่คือ 5 วิธีในการแปลงวันที่ในรูปแบบข้อความเป็นวันที่ใน Excel