ในการคำนวณหาระยะเวลาเป็นกี่ปี กี่เดือน กี่วัน นั้น หากใช้สูตร DateDif ในโปรแกรม Excel จะพบว่าระยะเวลาบางช่วงที่หาคำตอบนั้น จำนวนจะติดลบบ้าง จำนวนวันไม่ตรงกับที่เราคิดบ้าง ซึ่งจะเพี้ยนในช่วงวันที่ตั้งแต่วันที่ 28-31 เสมอ
John Walkenbach ได้แจกสูตร DateDiff ซึ่งผมได้เก็บไว้นานแล้ว เพิ่งค้นหาเจอจึงนำมาแจกให้พวกเราได้นำไปใช้กันครับ สูตรนี้หาระยะเวลาใกล้เคียงแบบที่คนเข้าใจกันมากกว่าสูตรของ Microsoft โดยเฉพาะในการนับวันสิ้นเดือนชนสิ้นเดือน
ปัจจุบันเว็บของ John Walkenbach ปิดตัวไปแล้ว ในช่วงที่ยังเปิดเว็บอยู่นั้น ระยะหลังก็ไม่พบว่ามีสูตร DateDiff นี้แจกเช่นเดิม ไม่ทราบว่าด้วยเหตุใดเหมือนกัน จึงขอให้ใช้สูตรนี้อย่างระมัดระวังไว้สักหน่อย
สูตรนี้ต่างจากสูตรเดิมตรงที่ลงท้ายด้วย f 2 ตัว มีโครงสร้างสูตร =DateDiff(FromDate,ToDate)
โดยตอนที่สร้างให้สร้างลงไปในเซลล์ 3 เซลล์พร้อมกันแล้วกดปุ่ม Ctrl+Shift+Enter จะได้คำตอบเป็นกี่ปี กี่เดือน กี่วัน ให้ในแต่ละเซลล์ ซึ่งผมได้ดัดแปลงรหัสเป็นสูตรใหม่ ไม่ต้องสร้างแบบ array ก็ได้ครับ
=YearDiff(FromDate,ToDate)
=MonthDiff(FromDate,ToDate)
=DayDiff(FromDate,ToDate)
Download แฟ้มตัวอย่างตามภาพนี้ได้จาก
https://drive.google.com/file/d/1FDdqYP_y8a4hJOybDfwvfKhbXoNNVnCa/view?usp=sharing
ผมได้สร้างเป็น Add-in ไว้ด้วยครับ Download ได้จาก
==========================================
รหัส VBA ของ John Walkenbach
'Source from www.j-walk.com
Option Explicit
Function DATEDIFF(d1, d2) As Variant
Dim YEARDIFF As Integer
Dim MONTHDIFF As Integer
Dim DAYDIFF As Integer
Dim temp As Date
temp = Time
' Swap arguments, if necessary
If d1 > d2 Then
temp = d1
d1 = d2
d2 = temp
End If
' Do the year part
YEARDIFF = Year(d2) - Year(d1)
If DateSerial(Year(d2), Month(d1), Day(d1)) > d2 _
Then YEARDIFF = YEARDIFF - 1
' Do the month part
If Month(d2) > Month(d1) Then
If Day(d2) >= Day(d1) Then
MONTHDIFF = Month(d2) - Month(d1)
Else
MONTHDIFF = Month(d2) - Month(d1) - 1
End If
Else
If Day(d2) >= Day(d1) Then
MONTHDIFF = Month(d2) - Month(d1) + 12
If MONTHDIFF = 12 Then MONTHDIFF = 0
Else
MONTHDIFF = Month(d2) - Month(d1) + 11
End If
End If
' Do the day part
If Day(d2) >= Day(d1) Then
DAYDIFF = Day(d2) - Day(d1)
Else
DAYDIFF = Day(DateSerial(Year(d1), _
Month(d1) + 1, 1) - 1) - Day(d1) + Day(d2)
End If
' Revised by Somkiat Foongkiat
' in case date1 > date2 and date2 is end of month
If Day(d1) > Day(d2) Then
If Day(d2) = Day(DateSerial(Year(d2), Month(d2) + 1, 0)) Then
DAYDIFF = 0
MONTHDIFF = MONTHDIFF + 1
End If
End If
If Day(d1) = Day(DateSerial(Year(d1), Month(d1) + 1, 0)) Then
If Day(d2) = Day(DateSerial(Year(d2), Month(d2) + 1, 0)) Then
DAYDIFF = 0
End If
End If
' Create the variant array to be returned
DATEDIFF = Array(YEARDIFF, MONTHDIFF, DAYDIFF)
End Function
======================================
'Further developed by Somkiat Foongkiat
Function YEARDIFF(d1, d2) As Integer
YEARDIFF = Application.Index(DATEDIFF(d1, d2), 1, 1)
End Function
Function MONTHDIFF(d1, d2) As Integer
MONTHDIFF = Application.Index(DATEDIFF(d1, d2), 1, 2)
End Function
Function DAYDIFF(d1, d2) As Integer
DAYDIFF = Application.Index(DATEDIFF(d1, d2), 1, 3)
End Function
หากจะคิดสูตรใหม่ขึ้นมาใช้ ต้องกำหนดหลักการนับให้ชัดเจนก่อน
จะนับเป็น 1 ปี เมื่อใด
1/10/1996 - 1/10/1997 นับวันชนวัน
หรือ
1/10/1996 - 2/10/1997 นับถึงวันถัดไปจึงครบปี
จะนับเป็น 1 ปี 1 เดือนเมื่อใด
31/1/1996 - 28/2/1997 นับวันสิ้นเดือนชนกัน
หรือ
31/1/1996 - 29/2/1997 สมมตว่ากพมี 29 วัน
หรือ
31/1/1996 - 1/3/1997 นับเดือนกพเต็ม 1 เดือน
หรือ
31/1/1996 - 2/3/1997 ถ้ากพมี 29 วัน ต้องเพิ่มไปอีก 2 วัน
หรือ
31/1/1996 - 3/3/1997 นับถึงวันถัดไป
จะนับเป็น 1 วันเมื่อใด
1/10/1996 - 1/10/1996
หรือ
1/10/1996 - 2/10/1996
เรื่องหลักการนับนี่แหละครับที่สูตรลัดใดๆ อาจหาไม่ตรงกับหลักนับของผู้ใช้งานแต่ละคนแต่ละบริษัท
