อย่างที่ผมเคยย้ำเสมอว่า Excel มองวันที่เป็นแค่ตัวเลขจำนวนธรรมดาตัวหนึ่งที่เปลี่ยนหน้าตาไปเท่านั้น (รายละเอียดอ่านได้ที่นี่)

นอกจากนี้ Excel จะรู้จักวันที่ในรูปแบบที่มันคุ้นเคยเท่านั้น เช่น dd/mm/yyyy หรือ dd-mm-yyyy หรือ yyyy/mm/dd หรือ yyyy-mm-dd (กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai )

ดังนั้น ถ้าหากเราใส่วันที่ในรูปแบบที่ Excel ไม่รู้จัก เช่น 2016.12.09 มันก็จะไม่มองว่าเป็นวันที่หรอกนะ นอกจากนี้จะต้องระวังเรื่องการใส่เลขปีด้วย ว่าปกติแล้วควรใส่เป็นปี ค.ศ. เสมอ (ยกเว้นจะตั้งค่าใน Number Format ให้รับ Input เป็น พ.ศ. ไทยไปเลย มีบอกวิธีท้ายบทความ)

แต่บางครั้งเราได้ข้อมูลมาจากคนอื่น (หรือแม้แต่ฐานข้อมูลที่ Office) ซึ่งดันมีข้อมูลวันที่อยู่ในรูปแบบที่แย่ๆ Excel มันก็เลยไม่รู้จัก เราก็เลยต้องยื่นมือไปช่วยหน่อยแล้ววววว!!!

นี่คือ 7 วิธีที่จะรับมือกับวันแย่ๆ ครับ

สมมติว่ากรอกวันที่แย่ๆ อยู่ในช่อง A1 นะครับ

วันแย่แบบที่ 1 : YYYYMMDD โดยปีเป็น ค.ศ.

วิธีแก้อาการนี้ ทำง่ายๆ ครับ โดยใช้ความรู้เรื่องฟังก์ชั่น TEXT กับ Custom Format มาช่วย ได้ดังนี้

=TEXT(A1,”0000-00-00″)*1 จะได้เป็นตัวเลขที่ถูกต้องแล้ว แต่ถ้าอยากเห็นเป็นวันที่ ก็ให้ Format เป็นวันที่เป็นอันจบ

วันแย่แบบที่ 2 : YYYYMMDD โดยปีเป็น พ.ศ.

วิธีแก้ ผมแนะนำ 2 วิธีแล้วกัน

แบบแรก ตัดทีละส่วนแล้วรวมกันด้วย DATE จะได้ว่า

=DATE(LEFT(A1,4)-543,MID(A1,5,2),RIGHT(A1,2))

แบบที่สอง ใช้ TEXT เหมือนเดิม จะได้ว่า

=TEXT((LEFT(A1,4)-543)&RIGHT(A1,4),”0000-00-00″)*1

วันแย่แบบที่ 3 : DDMMYYYY โดยปีเป็น ค.ศ.

คล้ายกับวิธีที่ 1 ครับ แต่ปรับ Format เล็กน้อย

=TEXT(A1,”00-00-0000″)*1

วันแย่แบบที่ 4 : DDMMYYYY โดยปีเป็น พ.ศ.

คล้ายกับวิธีที่ 2 ครับ มี 2 วิธี

แบบแรก =DATE(RIGHT(A1,4)-543,MID(TEXT(A1,”00000000″),3,2),LEFT(TEXT(A1,”00000000″),2))

แบบที่สอง = TEXT(LEFT(TEXT(A1,”00000000″),4)&RIGHT(A1,4)-543,”00-00-0000″)*1

วันแย่แบบที่ 5 : มีสัญลักษณ์ประหลาด เช่น .

กรณีเป็น ค.ศ :

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

=SUBSTITUTE(A1,”.”,”-“)*1

กรณีปีเป็น พ.ศ.

หลักการก็คือทำให้ . หายไปก่อนด้วย =SUBSTITUTE(A1,”.”,””) แล้วเอาไปเข้ากรณีวันแย่แบบที่สองครับ ซึ่งจะได้ว่า

=DATE(LEFT(A1,4)-543,MID(SUBSTITUTE(A1,”.”,””),5,2),RIGHT(A1,2))

อีกแบบก็จะได้ว่า

=TEXT((LEFT(A1,4)-543)&RIGHT(SUBSTITUTE(A1,”.”,””),4),”0000-00-00″)*1

วันแย่แบบที่ 6 : เลขปีเขียนแบบย่อ จาก YYYY เหลือแค่ YY 2 ตัวหลัง

เช่น DDMMYY โดยปีเป็น ค.ศ. แบบนี้เราก็ต้องรู้ก่อนว่า ปีเป็น 19YY หรือ 20YY

ถ้ามั่นใจว่าเป็น 20YY ก็ทำแค่นี้พอ =TEXT(A1,”00-00-00″)*1

แต่ถ้าเป็น 19YY ก็อาจจะต้องเติมตัวเลข 19 ให้มัน เช่น =DATE(“19″&RIGHT(A1,2),MID(TEXT(A1,”000000″),3,2),LEFT(TEXT(A1,”000000”),2))

วันแย่แบบที่ 7 : ข้อมูลต้นทางมีหลายรูปแบบผสมผสานกัน!!

แบบนี้ ถ้าคิดว่ามีเครือ่งหมายประหลาดอะไรก็แนะนำว่าให้ใช้ SUBSTITUTE ตัดทิ้งให้หมดก่อน

หลังจากนั้นคุณต้องเขียนสูตรเพื่อตรวจเช็ครูปแบบแล้วล่ะ
เช่น ใช้ LEFT RIGHT เช็คดูว่าเลขน่าจะเป็น ค.ศ. หรือ พ.ศ.? และมีกี่ตัวอักษร??

จากนั้นค่อยเอาไปใช้ IF หรือ CHOOSE เพื่อเลือกสูตรที่เหมาะสมต่อไป

แต่ถ้าเกิดเหตุการณ์แบบนี้จริงๆ ไปแก้ตั้งแต่ต้นเหตุดีกว่า ว่าทำไมถึงกรอกข้อมูลได้มั่วขนาดนี้!!!!

อย่างน้อยก็ควรใส่ Data Validation ไว้บ้างนะครับ เช่น

เทคนิคแถม : คนกรอกข้อมูลดึงดันจะกรอกเป็น พ.ศ.

อย่างที่บอกว่าเราควรกรอกข้อมูลเป็น ค.ศ. เสมอ แต่ถ้าคนกรอกดึงดันว่าจะกรอก พ.ศ.ล่ะ !!?

วิธีที่ทำให้กรอกข้อมูลเป็น พ.ศ. แล้วข้อมูลไม่เพี้ยน คือต้องเลือก Cell ช่องนั้นๆ แล้วเลือก Number Format ดังนี้ครับ

เป็นอย่างไรกันบ้างครับ กับเทคนิคการรับมือกันวันแย่ๆ ใน Excel ผมหวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ

หากใครมีเทคนิคอื่นๆ ในการจัดการกับปัญหาเรื่องวันที่ อย่าลืมแชร์กันให้ผมและเพื่อนๆ รู้บ้างนะครับ เพราะปัญหาหนึ่งๆ มันมีหลายทางแก้ไขแน่นอน ^^

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ
==========================================