เราจะสามารถแปลงวันที่ที่กรอกมาเป็น พ.ศ. (วิธีที่ผิด) ให้กลายเป็น ค.ศ. (แบบที่ถูกต้อง) ได้อย่างไร มาดูกันครับ
บางครั้งเวลาเรากรอกข้อมูลใน Excel โดยตั้งใจกรอกเป็นวันที่ 31 มกราคม ปี พ.ศ. 2557 เราก็เลยกรอกลงไปว่า 31/01/2557 แต่สิงที่ Excel เข้าใจ คือ มันจะมองว่าเป็น ค.ศ. 2557 (หรือ พศ. 3100 )ต่างหาก!! ไม่ใช่ พ.ศ. 2557 อย่างที่เราอยากได้ (อันนี้ผิดในแง่ข้อมูลเลยนะครับ ไม่ใช่เรื่องของ Format)
ซึ่งบางคนคิดว่าไม่เห็นเป็นไร เราเข้าใจว่าเป็น พศ. 2557 เองก็ได้ อันนี้เป็นความคิดที่ไม่ถูกต้องครับ เพราะทุกอย่างจะผิดเพี้ยนไปหมด ทั้งวันที่นี้คือวันจันทร์ อังคาร พุธ หรืออะไรก็จะผิดหมด แถมบางปีมี 29 กพ. โผล่มาอีกทั้งๆที่ปีนั้นถ้าอยู่ถูกปฏิทินจะมีแค่ 28 กพ. ดังนั้นการแก้วันที่ให้ถูกปฏิทินจึงเป็นเรื่องสำคัญมาก
บางทีเราอาจมี Input ทำนองนี้อยู่มากมาย (เช่น Import มาจาก Database อื่นแล้วผิด Format มาเลย) จะไล่แก้ทีละอันก็ใช่เรื่อง… วันนี้ผมเลยมีวิธีแปลงข้อมูลพวกนี้ให้ถูกต้อง โดยแก้ปัญหา พศ. กับ คศ. แบบง่ายๆ มาให้ครับ
จริงๆทำได้หลายวิธีมาก ผมขอแนะนำซัก 2 แบบละกัน
แปลงวันที่วิธีแรก ใช้ฟังก์ชั่น EDATE มาช่วย
สมมติ ในช่อง A1 เป็น 31/01/2557 (excel เข้าใจว่าเป็น คศ. 2557 ) แล้วเราจะแปลงให้เป็น 31/01/2014 โดยใช้สูตรมาช่วย มาดูกันครับว่าทำยังไง
วิธีคือ =EDATE(ช่องวันที่,-543*12)
เดี๋ยวผมจะอธิบายว่าทำไมเขียนแบบนี้ครับ
ก่อนอื่นต้องรู้จักฟังก์ชั่น EDATE ซะก่อนครับ… คร่าวๆคือมันสามารถเพิ่ม/ลด วันเข้าไปในวันที่ที่เรากำหนด โดยมีหน่วยเป็นเดือน โดยไม่สนว่าแต่ละเดือนจะมีกี่วัน เช่นจะมี 28, 29, 30 หรือ 31 วันก็ไม่สน
วิธีการเขียนสูตร EDATE
= EDATE(start_date,months)
ตัวอย่างการทำงานของฟังก์ชั่น EDATE
ให้ A1 = 31/01/2013
- ถ้าใส่เลข months เป็นบวก มันจะเพิ่มเวลาไปในอนาคต เท่ากับจำนวน months
- EDATE(A1,1) => 28/2/2013 (ไม่มีวันที่ 31/2/2013 จึงปรับเป็นวันสิ้นเดือนให้)
- EDATE(A1,2) => 31/3/2013
- EDATE(A1,3) => 30/4/2013 (ไม่มีวันที่ 31/4/2013 จึงปรับเป็นวันสิ้นเดือนให้)
- EDATE(A1,4) => 31/5/2013
- ถ้าใส่เลข months ติดลบ มันจะย้อนอดีตเท่ากับจำนวน months
- EDATE(A1,-1) => 31/12/2012
- EDATE(A1,-2) => 30/11/2012 (ไม่มี 31/11/2012)
ใช้ EDATE แปลงจาก พ.ศ. เป็น ค.ศ.
ที่นี้ถ้าเลขต้นฉบับ สมมติช่อง C1 เป็น 31/01/2557 แล้วเราจะทำให้เป็น 31/01/2014
แปลว่ามันต้องย้อนอดีตไป 543 ปี => นั่นคือ 543*12 เดือน
ดังนั้นเราจะเขียนสูตรว่า
= EDATE(C1,-543*12) นั่นเองครับ (ติดลบ เพราะเลขต้องน้อยลง)
เท่านี้ก็จะได้เป็นวันที่ 31/01/2014 แล้ว (ถ้าเห็นเป็นเลขหลักหมื่นให้ลองเปลี่ยน Format เป็น Date ดูครับ)
ข้อสังเกต!! วิธีนี้กรณีของวันที่ 29 กพ. เช่น เดิมใส่เป็น 29/02/2556 ถ้าใช้สูตรนี้แปลงจะได้เป็น 28/02/2013 ซึ่งต่างจากวิธีล่างที่่จะได้วันที่ 01/03/2013 แทน ซึ่งผมมองว่าวิธีบนถูกต้องมากกว่าครับ เพราะเป็นเดือนเดียวกันด้วย แต่ก็แล้วแต่สถานการณ์นะครับ
แปลงวันที่วิธีที่ 2 ใช้ฟังก์ชั่น DATE, YEAR, MONTH, DAY
วิธีนี้เป็นวิธีที่เห็นได้ทั่วไป ซึ่งผมมองว่าค่อนข้างยุ่ง เพราะต้องเขียนเยอะ คือ แตกปี เดือน วันออกมา แล้วลบที่ปีไปด้วยเลข 543
นั่นคือ
- ปี = YEAR(ช่องวันที่)-543
- เดือน = MONTH(ช่องวันที่)
- วัน = DAY(ช่องวันที่)
- แล้วจับรวมกันด้วยฟังก์ชั่น DATE(year,month,day) จะได้ว่า
- =DATE(YEAR(ช่องวันที่)-543,MONTH(ช่องวันที่),DAY(ช่องวันที่))
- ถ้าเห็นเป็นเลขหลักหมื่นให้ลองเปลี่ยน Format เป็น Date ดูครับ
ข้อสังเกต!! วิธีนี้กรณีของวันที่ 29 กพ. เช่น เดิมใส่เป็น 29/02/2556 ถ้าใช้สูตรนี้แปลงจะได้เป็น 01/03/2013 แทน ซึ่งต่างจากวิธีบน ที่่จะยังได้วันที่ 28/02/2013 ซึ่งผมมองว่าวิธีบนถูกต้องมากกว่าครับ เพราะเป็นเดือนเดียวกันด้วย แต่ก็แล้วแต่สถานการณ์นะครับ
