อธิบายการทำงาน DATEDIF ใน Excel และแนวทางแก้ไขให้ได้ผลลัพธ์ดั่งใจ

หนึ่งในฟังก์ชันที่ลึกลับที่สุดใน Excel สำหรับผมคือฟังก์ชันที่ชื่อว่า DATEDIF ครับ มันคือฟังก์ชันที่สามารถคำนวณระยะห่างระหว่างวันที่สองวันที่กำหนดได้ โดยสามารถแสดงผลลัพธ์เป็นระยะห่างได้หลายรูปแบบมากๆ เช่น ห่างเต็มวัน เต็มเดือน เต็มปี หรือแบบแปลกๆ เช่น นับวันแบบไม่สนใจเดือน และแบบอื่นที่แปลกๆ อีกมากมาย แต่มันดันกลายเป็นฟังก์ชันลึกลับที่ทาง Microsoft ไม่ค่อยอยากให้คุณใช้? ทำไมถึงเป็นแบบนั้น? และเราจะแก้ไขผลลัพธ์แปลกๆ จาก DATEDIF ยังไง มาดูกันครับ (Edit : มีสรุปสูตรให้ท้ายบทความ)

หมายเหตุ: บอกไว้ก่อนว่า DATEDIF ใน DAX ของ Power BI ไม่ได้ทำงานแบบเดียวกับฟังก์ชันใน Excel นะ มันเป็นอีกแบบโดยสิ้นเชิงเลย ในบทความนี้จะเป็นการพูดถึง DATEDIF ใน Excel นะครับ

ฟังก์ชันลึกลับ?

ที่ผมบอกว่ามันลึกลับเพราะว่ามันเป็นฟังก์ชันที่ไม่ขึ้นใน List ของฟังก์ชันใน Excel ด้วยซ้ำ

เวลาพิมพ์ =DATE เพื่อดูว่ามีฟังก์ชันอะไรบ้าง? ก็หาเจ้าตัวนี้ไม่เจอ!

แต่ถ้าพิมพ์จนครบแล้วเปิดวงเล็บ จะพบว่ามันมีตัวตนอยู่จริงๆ (แต่ก็ไม่ได้แสดง Tool Tips อะไรออกมาเลย 555)

วิธีการใช้งานของ DATEDIF

DATEDIF( start_date, end_date, interval )

โดยที่ interval เป็นการเลือก Mode ของการแสดงผลลัพธ์ ซึ่งมีหลายแบบ ดังนี้

โหมดการทำงานของ DATEDIF

• y : จำนวน ปีเต็ม ระหว่าง start_date และ end_date
• m : จำนวน เดือนเต็ม ระหว่าง start_date และ end_date
• d : จำนวน วันทั้งหมด ระหว่าง start_date และ end_date
• md : จำนวน วันที่เหลือ หลังจากนับเดือนเต็มจาก start_date ถึง end_date (เหมือนคิด m แล้วค่อย d)
• ym : จำนวน เดือนที่เหลือ หลังจากนับปีเต็มจาก start_date ถึง end_date (เหมือนคิด y แล้วค่อย m)
• yd : จำนวน วันที่เหลือ หลังจากนับปีเต็ม จาก start_date ถึง end_date (เหมือนคิด y แล้วค่อย d)

ตัวอย่างการใช้งานดังรูป

พอเห็นแบบนี้แล้วหลายคนคงสงสัยว่า ทำไมถึงต้องเอาฟังก์ชันที่เจ๋งขนาดนี้ไปซ่อนให้ลึกลับด้วยล่ะ?

สาเหตุเพราะฟังก์ชันนี้มันมีพฤติกรรมแปลกๆ หลายอย่างเลย ทาง Microsoft ถึงกับเขียนไว้ใน support document เลยนะ ว่ามันน่ากลัวมาก 555 ลองไปอ่านดูได้

อาการแปลกๆ ของ DATEDIF

ซึ่งเอาจริงๆ แล้วอาการแปลกๆ ของ DATEDIF จะเกิดขึ้นถ้าเราเริ่มต้นในช่วงปลายเดือนครับ เพราะมัน ต้องพยายามจบเดือนในเลขวันเดียวกับวันเริ่มต้น แต่บางทีมันก็เป็นไปไม่ได้!

อาการนับจำนวนติดลบหากวันเริ่มอยู่ในช่วงปลายเดือน

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือนที่มี 31 วัน ก็จะดูไม่มีปัญหาอะไร ทุกอย่างดูดี โปรแกรมมองว่าครบวันพอดีที่วันที่ 31

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือนที่มี 30 วัน สังเกตว่ามันจะมองว่าครบเดือนเต็มๆ ในวันที่ 1 ของเดือนถัดไป (จินตนาการว่าถ้าเดือนเมษามี 31 วันจะจบวันนั้นแหละ)

สาเหตุเพราะว่ามันพยายามมองว่าการจบเดือน เปรียบเสมือนต้องจบเดือนในเลขวันเดียวกับวันเริ่มต้นนั่นเอง

ถ้าเริ่มวันที่ 31 แล้วจบแถวๆ สิ้นเดือน กพ. มันจะมองว่าครบเดือนเต็มๆ เลยไปอีก (จินตนาการว่าถ้าเดือน กพ. มี 31 วันจะจบวันนั้นแหละ) ส่งผลให้การนับจำนวนวันติดลบได้

และถ้ายิ่งปีที่กพ. มี 28 วัน (ก็ปีปกติแหละ) ยิ่งแปลกหนัก คือติดลบ 2 เลยทีเดียว เพราะมันจะมองว่าครบเดือนเต็มๆ ในวันที่ 3 (จินตนาการว่าถ้าเดือนกพ. มี 31 วันจะจบวันนั้นแหละ)

อาการแปลกๆ อันอื่น

ผมมองว่าอาการแปลกอื่นๆ เป็นเรื่องของความต้องการที่เจาะจงของแต่ละคน ซึ่งอาจจะต้องการไม่เหมือนกัน

เช่น บางครั้งเวลาผมไปสอนลูกค้า เค้าก็บอกว่าอยากให้เมื่อเริ่มสิ้นเดือน จบสิ้นเดือน (แม้เลขจะน้อยกว่า) เช่น เริ่ม 31 มค. จบ 30 เมษา แบบนี้ให้ถือว่าครบเต็มเดือนเดือน ณ วัน สิ้นเดือนนั้นๆ ไปเลย ซึ่งจะเห็นว่า DATEDIF จะยังไม่ได้มองว่าครบเดือน (แต่จะมาครบในวันที่ 1)

ถ้าคุณมีความต้องการแบบเดียวกับลูกค้าของผม มันก็ต้องมีการ Adjust สูตรให้มันทำงานให้ได้ดั่งใจ ซึ่งมีแนวทางดังนี้

แนวทางการปรับสูตร DATEDIF ให้ทำงานดั่งใจ

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

จะเห็นว่าเราสามารถทำให้ถือว่าครบเดือนในวันสิ้นเดือนได้ ซึ่งเอาไว้ใช้ adj แบบ m และ ym ได้ทั้งคู่เลย

เริ่มวันที่ 30 ก็ใช้ได้

ถ้าวันเริ่ม <=28 มันก็จะไม่มีการ adjust เพราะเลขวันเริ่มไม่ได้มากกว่าวันจบ (มันถูกต้องอยู่แล้ว)

การ adjust เรื่อง md ที่มีติดลบ

เราต้องถามตัวเองแหละว่าต้องการผลลัพธ์แบบไหนถึงเรียกว่าถูกใจเรา?

ผมคิดว่าถ้าเลขวันเริ่ม <=28 เราทุกคนไม่น่ามีปัญหากับผลลัพธ์ของ DATEDIF ดังนั้นมันจะเริ่มมีปัญหากับการเริ่มด้วยวันที่ 29,30,31 เท่านั้น

แต่เราลองมาดูก่อนว่า ถ้าเริ่มวันที่ 28 มันให้ผลลัพธ์แบบไหน ซึ่งจะพบว่าถ้าจบวันที่ 28 จะถือว่า md เป็น 0 แล้ววันถัดไปเป็น 1,2,3… ไปเรื่อยๆ

ดังนั้นถ้าเริ่มวันที่ 29 เราก็น่าจะอยากให้ถ้ามันจบวันที่ 29 ก็เป็น 0 แล้วไล่ไปเรื่อยๆ แต่ปรากฏว่ามันไม่ได้ทำงานแบบนั้น โดยเฉพาะหากเดือนก่อนหน้ามีไม่ถึงวันที่ 29

แปลว่า ถ้าจำนวนวันในเดือนก่อนหน้าวันจบ น้อยกว่าเลขวันเริ่ม และเลขวันเริ่มมากกว่าเลขวันจบ เราจะเอาเลขวันของวันจบมาแทน md ไปเลย ดังนั้นเขียนสูตรได้ดังนี้

ซึ่งจะเห็นว่าผลลัพธ์ได้ดั่งใจแล้ว

ลองเปลี่ยนเลขไปเริ่ม 31 ว่า work มั้ย ก็พบว่าน่าจะได้ดั่งใจแล้วนะ

ลองเลขน้อยๆ ก็ดู ok ไม่ได้มีการ adjust อะไร

แต่ถ้าเรา adjust เรื่องเดือนตอนสิ้นเดือนไปแล้ว การนับวันก็ควรเป็น 0 วันด้วย ดังนั้นจะต้องปรับสูตรนับวันให้สอดคล้องใหม่อีกที ซึ่งผมได้แสดงวิธีแก้ไขให้ใน section สรุปสูตรข้างล่างนี้แล้วครับ

สรุปสูตรทั้งหมด

คำนวณปี

=DATEDIF(วันเริ่ม,วันจบ,"y")

คำนวณเดือน

=IF(AND(DAY(วันเริ่ม)>DAY(วันจบ),วันจบ=EOMONTH(วันจบ,0)),
DATEDIF(วันเริ่ม,วันจบ,"ym")+1,DATEDIF(วันเริ่ม,วันจบ,"ym"))

คำนวณวัน

=IF(DAY(วันเริ่ม)>DAY(วันจบ),IF(วันจบ=EOMONTH(วันจบ,0),0,
IF(DAY(EOMONTH(วันจบ,-1))<DAY(วันเริ่ม),DAY(วันจบ),DATEDIF(วันเริ่ม,วันจบ,"md"))))

ตัวอย่างการคำนวณ

ลองคำนวณ อายุงาน เป็นจำนวน ปี เดือนวัน ด้วยสูตรใหม่ดูได้ดังนี้

ผมสามารถแก้สูตร DATEDIF ให้ได้ผลลัพธ์ดั่งใจ(ผม)แล้ว แต่จะให้ได้ดั่งใจคุณรึเปล่าไม่รู้นะครับ เพราะแต่ละคนชอบไม่เหมือนกัน อาจต้องหา Logic มาปรับให้ตรงใจคุณอีกทีนะ ^^