อย่างที่ผมเคยย้ำเสมอว่า 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 ผมหวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ
หากใครมีเทคนิคอื่นๆ ในการจัดการกับปัญหาเรื่องวันที่ อย่าลืมแชร์กันให้ผมและเพื่อนๆ รู้บ้างนะครับ เพราะปัญหาหนึ่งๆ มันมีหลายทางแก้ไขแน่นอน ^^
