เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Power Query

เรื่องประหลาดเกี่ยวกับวันที่ของ Excel vs Power Query

เรื่องประหลาดเกี่ยวกับวันที่ของ Excel vs Power Query 1

หลายๆ คนน่าจะพอรู้อยู่ว่าใน 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 เท่านั้น (โชคดีไป)

ผมลองทำตารางเปรียบเทียบให้ดูแล้วดังนี้

เรื่องประหลาดเกี่ยวกับวันที่ของ Excel vs Power Query 2

จะเห็นว่า

  • เลข 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 จะต้องระวังว่าค่าวันที่อาจจะผิดเพี้ยนได้นะครับ (อย่างไรก็ตามเรื่องวัน จันทร์-อาทิตย์ ยังตรงกันอยู่ครับ)

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 199
  •  
  •  
  •  
  •  
  • 199
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply