“อยากรู้ว่าพนักงานคนนี้มีอายุงานกี่ปี ต้องเขียนสูตรยังไงคะ?” เจ้าของเสียงเล็ก ๆ คือน้องนิจิคนงาม
“ใช้ฟังก์ชัน YEARFRAC ได้เลยครับ”

เช่น เขียนสูตรเป็น

=YEARFRAC(A2,B2)

“ว้าย! ง่ายแค่นี้เอง” น้องนิจิทำท่าตกใจ
“ใช่ครับ ง่ายแบบนี้เลย” ผมยิ้ม ^_^

“เอ.. แล้วถ้าเราจับวันที่ลบกัน แล้วหารด้วย 365 ได้ไหมคะ?”
“ได้ครับ แต่ผลลัพธ์จะต่างกันนิดหน่อย”

“เอ๋! ทำไมล่ะคะ?”
“เพราะหลักการคำนวณของ YEARFRAC ไม่ได้นำจำนวนวันไปหารด้วย 365 ครับ”

มาดูโครงสร้างของฟังก์ชัน YEARFRAC กันนิดนึง

YEARFRAC(start_date,end_date,[basis])

start_date คือวันเริ่มต้น เช่น A2
end_date คือวันสิ้นสุด เช่น B2

[basis] คือโหมดการคำนวณ มีทั้งหมด 5 แบบ

0: หรือไม่ระบุ คือการคำนวณแบบ US (NASD) โดยถือว่าทุกเดือนมี 30 วัน และหนึ่งปีมี 360 วัน
1: คือการคำนวณโดยหารด้วยจำนวนวันเฉลี่ยในช่วงปีนั้น ๆ
2: คือการคำนวณโดยหารด้วย 360
3: คือการคำนวณโดยหารด้วย 365
4: คือการคำนวณแบบ European โดยถือว่าทุกเดือนมี 30 วัน และหนึ่งปีมี 360 วัน

(คำว่า Actual ที่เห็นใน function argument คือ end_date – start_date)

ดังนั้น YEARFRAC(A2,B2)
แท้จริงแล้วคือ YEARFRAC(A2,B2,0)
เป็นการมองว่าทุกเดือนมี 30 วัน และหนึ่งปีมี 360 วัน
ถ่านำจำนวนวันผลต่างไปหารด้วย 365 จะได้ผลลัพธ์ไม่เท่ากัน

แต่ถ้าระบุ basis เป็น 3 จะได้ผลลัพธ์เท่ากัน เพราะ basis 3 ถือว่าหนึ่งปีมี 365 วัน

หรือถ้าระบุ basis เป็น 2 ก็คือหารด้วย 360 นั่นเอง

ดังนั้นถ้าระบุ basis ต่างกัน ผลลัพธ์ที่ได้ก็ต่างกัน

“เอ๋! นิจิลองใช้ basis = 1 แล้วคิดแมนวลเทียบกัน ทำไมได้ผลลัพธ์ไม่เท่ากันล่ะคะ?

เพราะเอกซ์เซลไม่ได้คิดแยกเป็นรายปี แต่จับ end_date ลบ start_date แล้วหารด้วยจำนวนวันเฉลี่ยในช่วงปีนั้น ๆ

เช่น สมมติให้ start_date คือวันที่ 1-Jan-2020
แล้วให้ end_date คือวันที่ 1-Jul ของปี 2020, 2021, 2022, 2023, 2024

ถ้าใช้ YEARFRAC basis =1 จะได้ผลลัพธ์แบบนี้

ลองมาคิดแบบแมนวลบ้าง

คำนวณจำนวนวันเฉลี่ยของแต่ละช่วงปี (คิดเฉลี่ยตั้งแต่ปี 2020) จำนวนวันเฉลี่ยของแต่ละช่วงคือ

จับ end_date ลบด้วย start_date แล้วหารด้วยจำนวนวันเฉลี่ย จะพบว่าได้ผลลัพธ์เท่ากับ YEARFRAC basis 1 เป๊ะเลย!

“โอ…เพิ่งทราบว่าเอกซ์เซลคิดซับซ้อนขนาดนี้” น้องนิจิส่งสายตาประหลาดใจ
“ใช่ครับ หลักการของ basis 1 ค่อนข้างซับซ้อนและอธิบายยาก เอกซ์เซลจึงกำหนดค่าดีฟอลต์เป็น 0”

“คือส่งสัยนิดนึงค่ะ basis 0 กับ basis 4 ต่างกันยังไงหรือคะ?”

หลักการของ basis 0 กับ basis 4 เหมือนกัน คือคิดว่าทุกเดือนมี 30 วัน และหนึ่งปีมี 360 วัน แต่จะต่างกันเล็กน้อยกรณี end_date เป็นวันที่ 30 กับ 31

ถ้าใช้ basis 0 จะมองว่าเดือนนึงมี 30 วัน
ดังนั้นจึงมองวันที่ 31 เป็นวันที่ 1 ของเดือนถัดไป
ผลลัพธ์จากการใช้ end_date เป็นวันที่ 31 และวันที่ 1 จึงเท่ากัน

แต่ถ้าใช้ basis 4 จะมองว่าเดือนนึงมี 30 วัน ดังนั้นวันที่ 31 ก็คือวันที่ 30
ผลลัพธ์จากการใช้ end_date เป็นวันที่ 30 และ 31 จึงเท่ากัน (แต่ไม่เท่ากับวันที่ 1)

ลองเปรียบเทียบกันจะเห็นชัดเจน

“งั้นเราควรใช้ basis แบบไหนดีคะ?”
“แม้ว่าผลลัพธ์ของ basis แต่ละแบบจะต่างก้น แต่ก็ต่างกันน้อยมาก ๆ ถ้าไม่ซีเรียส ไม่ต้องระบุก็ได้ครับ ง่ายดี” ผมยิ้ม

“ขออนุญาตถามเพิ่มได้ไหมคะ” น้องนิจิโค้งตัวลง
“ยินดีครับ”
“แล้วถ้านิจิไม่ต้องการทศนิยมล่ะคะ ต้องการเฉพาะปีอย่างเดียว เช่น 1.35 ต้องการเป็น 1.00?”
“ถ้าเช่นนั้น ให้ครอบด้วยฟังก์ชัน INT เพื่อตัดเศษทิ้งได้เลยครับ”

เช่น เขียนสูตรเป็น

=INT(YEARFRAC(A6,B6))

“หวาว!” น้องนิจิอุทานเบา ๆ

“ขอถามอีกนิดนึงนะคะ แล้วถ้าต้องการทั้งปีและเดือนล่ะคะ เช่น 1Y 4M?”
“ถ้าเช่นนั้น ให้ใช้ฟังก์ชัน DATEDIF ครับ”

เช่น เขียนสูตรเป็น

=DATEDIF(A6,B6,"y")&"Y "
&DATEDIF(A6,B6,"ym")&"M"

(อ่านรายละเอียดการใช้ฟังก์ชัน DATEDIF ได้จาก บทความนี้)

“ว้าว! ได้ความรู้เยอะมากเลย ขอบคุณมาก ๆ นะคะ” น้องนิจิโค้งขอบคุณ
“ยินดีครับ” ผมยิ้มส่งเธอ แม้ในใจอยากคุยต่อก็ตาม..

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

ถ้าชอบ อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^

Reference:
https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8
https://exceljet.net/excel-functions/excel-yearfrac-function