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

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

datedif excel function

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

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

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

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

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

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

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

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

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

DATEDIF( start_date, end_date, interval )

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

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

  • d : ห่างกันกี่วัน
  • m : ห่างกันกี่เดือน (เต็มเดือน)
  • y : ห่างกันกี่ปี (เต็มปี)
  • md : ห่างกันกี่วัน (ไม่สนใจเดือน เสมือนอยู่เดือนเดียวกัน)
  • yd : ห่างกันกี่วัน (ไม่สนใจปี เสมือนอยู่ปีเดียวกัน)
  • ym : ห่างกันกี่เดือน (ไม่สนใจปี เสมือนอยู่ปีเดียวกัน)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

แต่ถ้าเรา 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 ใน Excel และแนวทางแก้ไขให้ได้ผลลัพธ์ดั่งใจ 18

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

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 959
  •  
  •  
  •  
  •  
  • 959
  •  
  •  
  •  
  •  
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