แปลง Text เป็น Date Excel ทำอย่างไร? แก้วันที่เป็นข้อความในไม่กี่ขั้นตอน

เคยได้รับไฟล์จาก ERP, SAP, โปรแกรมบัญชี, HR หรือ CSV แล้วพบว่า Sort วันที่ไม่ถูกต้อง คำนวณจำนวนวันไม่ได้ หรือ Pivot Table Group เดือน/ปีไม่ได้ไหมครับ? หนึ่งในสาเหตุสำคัญคือข้อมูลที่ดูเหมือนวันที่ถูกเก็บเป็นข้อความ (Text)

วันที่จริงของ Excel ถูกเก็บเป็นตัวเลข Serial Number แล้วใช้ Number Format กำหนดหน้าตา เช่น 31/08/2026 ส่วนข้อความที่มีหน้าตาเหมือนกันอาจยังไม่ใช่วันที่จริง จึงต้องตรวจสอบชนิดข้อมูลก่อนครับ

สมมติข้อมูลอยู่ที่ A2 และแสดงเป็น 31/08/2026 เราสามารถใช้ ISTEXT และ ISNUMBER ตรวจสอบค่าที่เก็บอยู่ได้ครับ

=ISTEXT(A2)

=ISNUMBER(A2)

ลองสร้างตารางนี้ โดยกำหนด A2 เป็นข้อความ และ A3 เป็นวันที่จริงที่กรอกผ่าน Excel ครับ

แถว 2 และ 3 อาจมองเห็นเหมือนกัน แต่ชนิดข้อมูลต่างกัน คอลัมน์ C และ D เป็นผลลัพธ์ตัวอย่างของสูตร ISTEXT และ ISNUMBER

ตรวจด้วย General อีกวิธีหนึ่ง

เลือกเซลล์แล้วเปลี่ยน Number Format เป็น General หากเป็นวันที่จริง Excel จะแสดง Serial Number เช่น วันที่ 31 สิงหาคม 2026 มีค่า Serial 46265 ในระบบวันที่ 1900 ที่ใช้ทั่วไป แต่ถ้าเป็น Text มักยังคงแสดงเป็นข้อความเดิม

สำหรับข้อมูลที่มีรูปแบบเหมือนกันทั้งคอลัมน์ เช่น DD/MM/YYYY ผมแนะนำให้เริ่มจาก Text to Columns ครับ เพราะสามารถระบุได้ชัดเจนว่าข้อมูลเรียงวัน เดือน ปีอย่างไร

สมมติข้อมูลต้นทางอยู่ที่ A2:A6 และต้องการเก็บต้นฉบับไว้ โดยให้ผลลัพธ์ออกที่คอลัมน์ B

หากต้องสร้างข้อมูลทดลองเอง ให้กำหนดคอลัมน์ A เป็น Text ก่อนกรอก หรือพิมพ์เครื่องหมาย apostrophe นำหน้าวันที่ เช่น '31/08/2026 เพื่อให้ Excel เก็บเป็นข้อความ โดยเครื่องหมายดังกล่าวจะไม่แสดงในเซลล์ตามปกติครับ

ตารางนี้เป็นตัวอย่างผลลัพธ์หลังแปลง โดย A เป็น Text ต้นทาง, B เป็น Date จริง, C และ D เป็นคอลัมน์ตรวจสอบ

ทำตามขั้นตอนนี้

1. ตรวจสอบและสำรองข้อมูลต้นฉบับ

   ยืนยันว่าข้อมูลเป็น Text และรู้รูปแบบวัน/เดือน/ปีจากระบบต้นทางก่อนเริ่ม

2. เลือก A2:A6

   เลือกเฉพาะช่วงข้อมูลวันที่ที่ต้องการแปลง

3. ไปที่ Data → Text to Columns

   เลือก Delimited แล้วกด Next

4. ตรวจสอบ Delimiter

   ถ้าไม่ได้ต้องการแยกคอลัมน์ ให้ตรวจสอบว่าไม่มีตัวคั่นที่จะแยกข้อมูลผิด แล้วกด Next

5. เลือก Column data format → Date

   เลือก DMY สำหรับข้อมูล 31/08/2026 เพราะต้นทางเรียงวัน/เดือน/ปี

6. กำหนด Destination แล้วกด Finish

   หากต้องการเก็บ A ไว้ ให้ตั้ง Destination เป็น $B$2 ตรวจสอบว่า B ว่าง และลองกับข้อมูลบางแถวก่อน

หลังแปลง ให้ตั้งคอลัมน์ B เป็น Date Format ที่ต้องการ แล้วตรวจสอบว่าค่าที่ได้เป็น Date จริงครับ

=ISNUMBER(B2)

=YEAR(B2)

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

ตัวอย่างที่ต้องระวัง: 03/04/2026

หากต้นทางเป็น DMY จะหมายถึง 3 เมษายน 2026 แต่ถ้าเป็น MDY จะหมายถึง 4 มีนาคม 2026 ทั้งสองแบบเป็นวันที่ถูกต้อง Excel จึงอาจไม่แสดง Error เลยครับ

แล้วข้อมูล พ.ศ. ล่ะ?

ถ้าต้นทางใช้ปี พ.ศ. เช่น 31/08/2569 ต้องตรวจสอบก่อนว่าเป็นข้อความปี พ.ศ. จริง หรือเป็นวันที่จริงที่เครื่องแสดงผลเป็น พ.ศ. อย่าเพิ่งบวกหรือลบ 543 โดยเดาครับ

หากข้อมูลเป็นข้อความปี พ.ศ. จริง ต้องแปลงปีให้เป็น ค.ศ. อย่างถูกต้องก่อนสร้าง Date ที่ใช้คำนวณต่อได้ ส่วนวันที่จริงที่เพียงแสดงเป็น พ.ศ. ไม่จำเป็นต้องเปลี่ยนค่า Serial Number ครับ

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

สมมติ A2 เป็น Text 31/08/2026 และเครื่องสามารถอ่านรูปแบบ DMY นี้ได้

=DATEVALUE(A2)

DATEVALUE จะคืนค่า Serial Number ของวันที่ จากนั้นกำหนด Number Format ของ B2 เป็น Date และลากสูตรลงแถวถัดไปได้ครับ

คอลัมน์ B แสดงผลเป็น Date หลังใช้ DATEVALUE ส่วนคอลัมน์ C สามารถใช้ =YEAR(B2) แล้วลากลงได้

ทำไม DATEVALUE ขึ้น #VALUE!?

สาเหตุหนึ่งคือ Excel ไม่สามารถตีความข้อความตามรูปแบบวันที่ที่คาดหวังได้ เช่น ข้อมูลเป็น DMY แต่เครื่องคาดหวัง MDY นอกจากนี้ข้อความที่ไม่ใช่วันที่หรือมีอักขระแปลกปลอมก็อาจทำให้แปลงไม่ได้ครับ

ข้อมูลจาก ERP หรือ Database บางระบบอยู่ในรูปแบบ 8 หลัก เช่น 20260831 หมายถึง 31 สิงหาคม 2026 กรณีนี้สามารถแยกปี เดือน วัน แล้วประกอบเป็น Date ได้ครับ

สมมติ A2 เป็น 20260831 (Text หรือตัวเลข 8 หลัก) และต้องการผลลัพธ์ใน B2

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

คอลัมน์ C ใช้ =ISNUMBER(B2) เพื่อตรวจสอบว่า B เป็นตัวเลขวันที่จริง และกำหนด B เป็น Date Format แล้ว

ตรวจสอบวันที่ที่ไม่ถูกต้องก่อนนำไปใช้

DATE สามารถปรับค่าที่เกินช่วงให้กลายเป็นวันอื่นได้ เช่น DATE(2026,2,31) จะได้ 03/03/2026 แทนที่จะขึ้น Error ดังนั้นการประกอบ Date สำเร็จจึงยังไม่รับประกันว่าข้อมูลต้นทางถูกต้องครับ

สำหรับข้อมูลรูปแบบ YYYYMMDD ที่ยืนยันว่ามี 8 หลักแล้ว สามารถใช้สูตรตรวจสอบว่าปี เดือน และวันหลังแปลงตรงกับต้นทางหรือไม่ โดยให้ B2 เป็นวันที่ที่สร้างจากสูตรด้านบน

=IF(AND(YEAR(B2)=--LEFT(A2,4),MONTH(B2)=--MID(A2,5,2),DAY(B2)=--RIGHT(A2,2)),"ถูกต้อง","ตรวจสอบวันที่")

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

งานที่ต้องทำซ้ำ: ใช้ Power Query

ถ้าต้องรับไฟล์รูปแบบเดิมทุกเดือน เราสามารถกำหนดขั้นตอนทำความสะอาดและแปลงชนิดข้อมูลไว้ใน Power Query แล้ว Refresh เมื่อข้อมูลใหม่เข้ามาได้ครับ

ตัวอย่าง ถ้าต้นทางเป็นข้อความ DMY ให้เลือกคอลัมน์วันที่ → Change Type → Using Locale → เลือก Data Type เป็น Date และ Locale ที่ใช้รูปแบบวันที่ตรงกับต้นทาง เช่น English (United Kingdom) สำหรับ DMY ที่เหมาะสมกับข้อมูลนั้น จากนั้นตรวจผลลัพธ์ก่อน Load กลับ Excel

สมมติฝ่าย HR Export ข้อมูลพนักงานจากระบบ โดย Start Date เป็น Text รูปแบบ DMY และต้องการสรุปจำนวนพนักงานเข้าใหม่รายเดือนครับ

ข้อมูลเริ่มแถว 2 คอลัมน์ C เป็นผลลัพธ์หลังแปลงจาก Text เป็น Date จริง โดยเก็บคอลัมน์ B ต้นทางไว้

1. ตรวจสอบ Start Date ต้นทาง

   ยืนยันว่าคอลัมน์ B เป็น Text และรูปแบบต้นทางเป็น DMY

2. แปลงเป็น Date จริงในคอลัมน์ C

   ใช้ Text to Columns โดยเลือก B2:B6 และกำหนด Destination เป็น C2 หรือใช้วิธีที่เหมาะกับข้อมูลต้นทาง

3. สร้างคอลัมน์ปีและเดือน

   เมื่อ C เป็น Date จริงแล้ว ใส่สูตรใน D2 และ E2 แล้วลากลง

=YEAR(C2)

=MONTH(C2)

จากนั้นสามารถนำ Start Date ที่แปลงแล้วไปใช้กับ Pivot Table เพื่อ Group ตามเดือน/ปี หรือใช้คอลัมน์ปีและเดือนในการสรุปรายงานได้ โดยต้องตรวจสอบว่าไม่มี Text หรือข้อมูลวันที่ผิดปะปนอยู่ครับ

Checklist ก่อนทำรายงาน

การแปลง Text เป็น Date Excel ไม่ยาก แต่สิ่งสำคัญกว่าการแปลงให้สำเร็จคือ ต้องได้วันที่ตรงกับข้อมูลต้นทางจริง ครับ หากเป็นงานครั้งเดียวที่รูปแบบสม่ำเสมอ Text to Columns มักเหมาะมาก ส่วนงานที่ต้อง Import ซ้ำทุกเดือนสามารถต่อยอดด้วย Power Query เพื่อบันทึกขั้นตอนและ Refresh ได้