เล่นแร่แปรสูตร : การแปลงวันที่ Text ให้เป็นวันที่ Date

ปกติแล้ว Excel จะมีฟังก์ชั่นที่ชื่อว่า DATEVALUE ในการเปลี่ยนวันที่ในรูปแบบ Text ให้กลายเป็นรูปแบบ Date จริงๆ ที่เป็นตัวเลขอยู่แล้ว… แต่ฟังก์ชั่นนี้มีข้อจำกัดอยู่มาก คือ มันจะ Convert Text ได้แค่ในรูปแบบที่มันรู้จักเท่านั้น (ซึ่งมีไม่กี่แบบ คล้ายๆตอนที่เราพิมพ์ลงไปใน cell ปกติ แหละครับ ว่า 31/1/2014 หรือ 31-Jan-2014 หรือ 31-01-2014 แล้ว excel มันจะฉลาดแปลงเป็นวันที่ได้เอง)

ดังนั้น ถ้าหากเรามี Date ในรูปแบบแปลกไปจากที่มันรู้จัก เช่น 31012014 หรือ 20140131 อะไรแบบนี้ ฟังก์ชั่นนี้ก็จะเอ๋อไปเลย

วันนี้ผมมีวิธีแก้มาแนะนำหลากหลายวิธีด้วยกันครับ ลองติดตามดูได้

วิธี 1 ตัด Text ออกเป็นส่วนๆ แล้วเชื่อม (ยาก)

Concept : ใช้พวกฟังก์ชั่น LEFT RIGHT MID หรือ Text to Column ช่วยตัดวันเดือนปีแยกออกจากกัน แล้วค่อยมาเชื่อมกันอีกทีด้วยฟังก์ชั่น DATE

สมมติว่า ต้นฉบับ อยู่ในช่อง A1  คือ “20140131” (ปีเดือนวัน)

• ตัดปี = LEFT(A1,4)
• ตัดเดือน = MID(A1,5,2)
• ตัดวัน =RIGHT(A1,2)
• จับรวมด้วย DATE (year,month,day)
  • =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

สมมติว่า ต้นฉบับ อยู่ในช่อง A1  คือ “31012014” (วันเดือนปี)

• ตัดวัน =LEFT(A1,2)
• ตัดเดือน = MID(A1,3,2)
• ตัดปี = RIGHT(A1,4)
• จับรวมด้วย DATE (year,month,day)
  • =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

วิธี 2 แปลง Format ของ Text ให้ DATEVALUE รู้จัก (ง่าย)

ในเมื่อฟังก์ชั่น DATEVALUE มันรู้จักรูปแบบแค่บางอย่าง เราก็ช่วยมันหน่อย โดยใช้ฟังก์ชั่น TEXT ช่วยแปลง FORMAT ให้

สมมติว่า ต้นฉบับ อยู่ในช่อง A1  คือ “20140131” (ปีเดือนวัน)

• ใช้ TEXT แปลง =TEXT(A1,”0000-00-00″)  <= ปี 4 หลัก เดือน 2 หลัก วันที่ 2 หลัก…
• ใช้ DATEVALUE แปลงค่าให้เป็น Date จริงๆ =DATEVALUE(TEXT(A1,”0000-00-00″))
• อาจได้ค่าออกมาเป็นตัวเลขธรรมดา ให้เปลี่ยน Format เป็น Date ก็จะเห็นเป็นวันที่ครับ

สมมติว่า ต้นฉบับ อยู่ในช่อง A1  คือ “31012014” (วันเดือนปี)

• ใช้ TEXT แปลง =TEXT(A1,”00-00-0000″) <= วันที่ 2 หลัก เดือน 2 หลัก ปี 4 หลัก
• ใช้ DATEVALUE แปลงค่าให้เป็น Date จริงๆ =DATEVALUE(TEXT(A1,”00-00-0000″))
• อาจได้ค่าออกมาเป็นตัวเลขธรรมดา ให้เปลี่ยน Format เป็น Date ก็จะเห็นเป็นวันที่ครับ