หลายๆ คนน่าจะพอรู้อยู่ว่าใน Excel นั้นจะมองวันที่เป็นแค่ Format ของค่าที่แท้จริงที่เป็นเลขจำนวนเต็มธรรมดา โดยเริ่มจากเลข 1 คือวันที่ 1/1/1900 (ค.ศ.) พอวันเพิ่มขึ้น 1 วัน ค่าที่แท้จริงก็จะเพิ่มขึ้น 1 หน่วยด้วย โดยวันที่สุดท้ายที่ Excel รองรับคือ 31/12/9999 (ค.ศ.)
แต่ว่าใน Power Query นั้นรองรับวันที่วันแรกได้เก่าถึง 1/1/0001 (ค.ศ.) ซึ่งจะไม่สามารถแสดงออกมาเป็นวันที่หรือตัวเลขใน Excel ได้เลย ส่วนวันที่สุดท้ายก็รองรับถึง 31/12/9999 (ค.ศ.) เช่นเดียวกับ Excel
ความผิดเพี้ยนอยู่ที่ต้นปี 1900
แต่ที่แปลกคือ ช่วงวันแรกๆ ของปี ค.ศ. 1900 นี่แหละ เจ้า Excel กับ Power Query จะมองเลขจำนวนเต็มเดียวกันเป็นคนละวัน!! โดยที่ความเพี้ยนนี้จะอยู่แค่ถึงสิ้น กพ. ปี ค.ศ. 1900 เท่านั้น (โชคดีไป)
ผมลองทำตารางเปรียบเทียบให้ดูแล้วดังนี้
จะเห็นว่า
- เลข 1 ใน Excel คือ 1/1/1900 แต่ PQ คือ 31/12/1899
- เลข 60 ใน Excel คือ 29/2/1900 แต่ PQ คือ 28/2/1900
- เลข 61 จะกลับมาเท่ากัน คือ 1/3/1900 ทั้งคู่!!
จะเห็นว่าความเพี้ยนนี้เกิดจากวันที่ 29 กพ. นี่เอง ที่ Excel มองว่าปี 1900 มี 29 กพ. แต่ PQ มองว่าไม่มี 29 กพ.
แล้วใครกันแน่ที่ถูกต้อง?
ตามหลักการคร่าวๆ คือ ถ้าปี ค.ศ. หารด้วย 4 ลงตัวจะมี 29 กพ. ซึ่งถ้าคิดแบบนั้น 1900 ก็หาร 4 ลงตัว มันก็ควรมี 29 กพ. ใช่มั้ย?
แต่จริงๆ แล้วหลักการมันมีเพิ่มละเอียดกว่านั้นอีก คือ ปีที่หารด้วย 100 ลงตัว แต่หารด้วย 400 ไม่ลงตัว จะไม่มี 29 กพ.
แปลว่าปี 1900 ก็ต้องไม่มี 29 กพ. ต่างหาก! (นั่นคือ 1700, 1800, 1900, 2100, 2200, 2300 จะไม่มี 29 กพ. ทั้งหมดเลย) แปลว่า Excel มันเทียบตารางวันที่ผิดไปอยู่นิดนึงนั่นเอง
สรุป
การที่ปี 1900 ต้องไม่มี 29 กพ. แต่ใน Excel ดันมี คือสาเหตุที่ Power Query ต้องแก้เรื่องนี้ให้ถูกต้อง ใน PQ จึงต้องไปเริ่มเลข 1 ที่วันที่ 31/12/1899 แทนที่จะเป็น 1/1/1900 เหมือนกับ Excel และยอมให้ค่าวันที่ไม่เหมือนกันอยู่ 2 เดือน หลังจากนั้นก็จะเท่ากันทุกประการไปจนถึง 31/12/9999 นั่นเอง
แปลว่าการดึงข้อมูลจาก Excel ที่มีวันที่ในช่วง 1/1/1900 – สิ้นกพ. 1900 จะต้องระวังว่าค่าวันที่อาจจะผิดเพี้ยนได้นะครับ (อย่างไรก็ตามเรื่องวัน จันทร์-อาทิตย์ ยังตรงกันอยู่ครับ)
