ที่ไปที่มาของสูตร DateDif

January 23, 2021


สูตรนี้เดิมทีเป็นสูตรที่ Lotus 1-2-3 มีอยู่ Microsoft สร้างสูตรนี้ขึ้นมาเพื่อทำให้ผู้ใช้ Lotus สามารถเปลี่ยนใจมาใช้ Excel ได้ง่ายขึ้น เป็นสูตรที่แอบบอกต่อกัน ไม่มีใน Help แต่คนที่ใช้ Excel ทราบกันว่ามีสูตรนี้อยู่ เรียกว่า undocumented function

Microsoft เคยตัดสูตรนี้ออกไปจาก Excel ในบาง version แต่จนแล้วจนรอดก็นำสูตรนี้มาใส่ไว้ให้ใช้กันอีก น่าจะเป็นเพราะยังมีผู้ใช้สูตรนี้อยู่นั่นเอง Excel บางรุ่นก็ใส่ไว้ใน Help บางรุ่นก็ไม่มีใน Help แถมบางรุ่นให้คำตอบต่างกันได้อีกถ้าใช้ Service pack ต่างรุ่นกัน
หลักการคำนวณของสูตร DateDif ใช้วิธีคิดแบบเดียวกับสูตร Date(year,month,day) เช่น

Date(2021,02,1) จะได้ 1/2/2021
Date(2021,02,0) จะได้ 31/1/2021
Date(2021,02,-1) จะได้ 30/1/2021
DATE(2021,0,0) จะได้ 30/11/2020
DATE(2021,-1,0) จะได้ 31/10/2020

การที่สูตร Date สามารถใช้ตัวเลขเดือนและเลขวันเป็นค่าบวก ลบ หรือศูนย์ก็ได้ ดังนั้นสูตร DateDif ก็สามารถคำนวณจำนวนระยะเวลาออกมาเป็นค่าลบหรือศูนย์ได้เช่นกัน ซึ่งเมื่อนำระยะเวลาที่สูตร DateDif คำนวณได้มาใช้คำนวณด้วยสูตร Date ต้องหาวันที่ตรงกัน

การคำนวณแบบนี้ ไม่ได้เป็น Bug หรือเป็นการคำนวณผิด แต่ควรถือว่าเป็นวิธีคิดของ Excel ซึ่งอาจไม่ตรงกับวิธีคิดของมนุษย์

ผมจะแนะนำให้เรียนรู้วิธีคิดของสูตรนี้ว่าต้องระวังอะไรบ้าง ผมจะสอนว่าไม่ควรนำสูตรนี้ไปใช้กับงานสำคัญ เพราะจะหาคำตอบเป็นค่าติดลบได้ด้วยและไม่ตรงกับที่คนคิด

คำแนะนำของ Microsoft บอกไว้ว่า ให้ใช้สูตรนี้ได้แค่ในระดับของปีและเดือน ไม่ควรใช้ถึงระดับวัน

ถ้าผมจะใช้สูตรนี้คำนวณหาระยะเวลากี่ปี กี่เดือน กี่วัน ผมจะปรับวันที่ให้เป็นวันที่ 1 ของทั้งวันเริ่มต้นและวันสุดท้าย จากนั้นจึงมาคำนวณหาจำนวณวันเองทีหลัง และที่สำคัญอย่าลืมเขียนกำกับไว้ว่า ประมาณ

ปล

แม้ในโลกแห่งความเป็นจริง เดือนกุมภา ไม่มีทางมี 30-31 วัน แต่ในโลกของ Excel สามารถใช้สูตร =Date(2021,2,31) ได้ด้วย ลองหาคำตอบกันเองครับว่าจะตอบว่าวันไหน

=Date(2021,191,911) แบบนี้ก็ยังได้ครับ ในโลกของ Excel

ด้วยเหตุเดียวกันนี่แหละ สูตร DateDif จึงให้คำตอบเป็น -1 วันหรือกี่ลบ X วันยังได้ ไม่ผิดหลักคำนวณของเขา

สูตร Excel ที่เขาออกแบบมาต้องมีสูตรคู่กันหรือตรงกันข้ามกันเพื่อหาค่าย้อนกลับมาได้ครับ เช่น Code vs Char, Ceiling vs Floor, DateDif vs Date

คนที่จะเก่ง Excel ได้ต้องคิดเองแบบนี้ พอใช้สูตรหนึ่งเป็นต้องหาสูตรที่คู่กันหรือทำงานตรงกันข้ามได้ด้วย

เรียน 1 ได้ 2 นี่เป็นเรื่องหนึ่งที่ผมจะสอนลูกศิษย์