DATEDIFFERENCE
Returns the number of days, months or years between two dates.
These replace the built-in function DATEDIF that is no longer supported.
Remarks
* For an illustrated example of the built-in DATEDIF Function refer to the page under Advanced Functions.
* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* The equivalent JavaScript function is DATEDIFFERENCE
DATEDIFFERENCE_D
Returns the number of whole days between "start_date" and "end_date".
Public Function DATEDIFFERENCE_D( _
ByVal dtStart_Date As Date, _
ByVal dtEnd_Date As Date) As Long
On Error GoTo ErrorHandler
Application.Volatile
DATEDIFFERENCE_D = VBA.Int(dtEnd_Date - dtStart_Date)
Exit Function
ErrorHandler:
DATEDIFFERENCE_D = -1
End Function
DATEDIFFERENCE_M
Returns the number of whole months (or months and days) (or months, weeks and days) between "start_date" and "end_date".
Public Function DATEDIFFERENCE_M( _
ByVal dtStart_Date As Date, _
ByVal dtEnd_Date As Date) As Long
Dim eom_start As Single
Dim eom_end As Single
Dim number1 As Single
Dim number2 As Single
Dim number3 As Single
On Error GoTo ErrorHandler
Application.Volatile
eom_start = Application.WorksheetFunction.EoMonth(dtStart_Date, 0)
eom_end = Application.WorksheetFunction.EoMonth(dtEnd_Date, 0)
number1 = (12 * (VBA.Year(dtEnd_Date) - VBA.Year(dtStart_Date))) - 12 + _
(12 - VBA.Month(dtStart_Date)) + VBA.Month(dtEnd_Date) - 1
number2 = (eom_start - dtStart_Date + 1) / VBA.Day(eom_start)
number3 = (1 - (eom_end - dtEnd_Date) / VBA.Day(eom_end))
DATEDIFFERENCE_M = VBA.Int(VBA.Round(number1 + number2 + number3, 1))
Exit Function
ErrorHandler:
DATEDIFFERENCE_M = -1
End Function
DATEDIFFERENCE_Y
Returns the number of whole years (or years and days) (or years, months and days) between "start_date" and "end_date".
Public Function DATEDIFFERENCE_Y( _
ByVal dtStart_Date As Date, _
ByVal dtEnd_Date As Date) As Long
Dim minusnumber As Integer
On Error GoTo ErrorHandler
Application.Volatile
If (VBA.Month(dtStart_Date) > VBA.Month(dtEnd_Date)) Then
minusnumber = 1
Else
If (VBA.Month(dtEnd_Date) = VBA.Month(dtStart_Date)) Then
If (VBA.Day(dtEnd_Date) < VBA.Day(dtStart_Date)) Then
minusnumber = 1
Else
minusnumber = 0
End If
Else
minusnumber = 0
End If
End If
DATEDIFFERENCE_Y = VBA.Year(dtEnd_Date) - VBA.Year(dtStart_Date) - minusnumber
Exit Function
ErrorHandler:
DATEDIFFERENCE_Y = -1
End Function
DATEDIFFERENCE_MD
Returns the number of days between "start_date" and "end_date" (ignoring months and years).
We have not used the VBA Operator because this value can be different to the Excel MOD function.
Public Function DATEDIFFERENCE_MD( _
ByVal dtStart_Date As Date, _
ByVal dtEnd_Date As Date) As Long
Dim minusnumber As Integer
Dim number1 As Long
Dim number2 As Long
On Error GoTo ErrorHandler
Application.Volatile
minusnumber = 0
If (VBA.Day(EndDate) = VBA.Day(dtStart_Date)) Then
DATEDIFFERENCE_MD = 0
Else
If (VBA.Day(EndDate) > VBA.Day(dtStart_Date)) Then
DATEDIFFERENCE_MD = VBA.Day(dtEnd_Date) - VBA.Day(dtStart_Date)
Else
number1 = VBA.DateSerial(VBA.Year(dtStart_Date), _
VBA.Month(dtStart_Date) + 1, _
VBA.Day(dtEnd_Date))
DATEDIFFERENCE_MD = number1 - dtStart_Date
End If
End If
Exit Function
ErrorHandler:
DATEDIFFERENCE_MD = -1
End Function
DATEDIFFERENCE_YD
Returns the number of days between "start_date" and "end_date" (ignoring years).
This function always takes the year from the "end_date".
Public Function DATEDIFFERENCE_YD( _
ByVal dtStart_Date As Date, _
ByVal dtEnd_Date As Date) As Long
Dim start_day As Long
Dim start_month As Long
Dim start_year As Long
Dim end_day As Long
Dim end_month As Long
Dim end_year As Long
Dim firstdate As Date
Dim modifylast_year As Long
Dim lastdate As Date
On Error GoTo ErrorHandler
Application.Volatile
start_day = Day(dtStart_Date)
start_month = Month(dtStart_Date)
start_year = Year(dtStart_Date)
end_day = Day(dtEnd_Date)
end_month = Month(dtEnd_Date)
end_year = Year(dtEnd_Date)
If (start_month = end_month) Then
If (start_day = end_day) Then
DATEDIFFERENCE_YD = 0
Else
If (start_day <= end_day) Then
lastdate = VBA.DateSerial(end_year, end_month, end_day)
firstdate = VBA.DateSerial(end_year, start_month, start_day)
DATEDIFFERENCE_YD = lastdate - firstdate
Else
If (start_year < (end_year - 1)) Then
lastdate = VBA.DateSerial(end_year + 1, end_month, end_day)
firstdate = VBA.DateSerial(end_year, start_month, start_day)
DATEDIFFERENCE_YD = lastdate - firstdate
Else
lastdate = VBA.DateSerial(end_year, end_month, end_day)
firstdate = VBA.DateSerial(end_year, start_month, start_day)
DATEDIFFERENCE_YD = lastdate - firstdate
End If
End If
End If
Else
If (start_month < end_month) Then
lastdate = VBA.DateSerial(end_year, end_month, end_day)
firstdate = VBA.DateSerial(end_year, start_month, start_day)
DATEDIFFERENCE_YD = lastdate - firstdate
Else
lastdate = VBA.DateSerial(end_year + 1, end_month, end_day)
firstdate = VBA.DateSerial(end_year, start_month, start_day)
DATEDIFFERENCE_YD = lastdate - firstdate
End If
End If
Exit Function
ErrorHandler:
DATEDIFFERENCE_YD = -1
End Function
DATEDIFFERENCE_YM
Returns the number of months between "start_date" and "end_date" (ignoring days and years).
Public Function DATEDIFFERENCE_YM( _
ByVal dtStartDate As Date, _
ByVal dtEndDate As Date) As Long
Dim minusnumber As Integer
Dim number1 As Long
Dim number2 As Long
On Error GoTo ErrorHandler
Application.Volatile
minusnumber = 0
If (VBA.Day(EndDate) < VBA.Day(dtStart_Date)) Then
minusnumber = 1
End If
number1 = (12 * (VBA.Year(dtEnd_Date) - VBA.Year(dtStart_Date))) + _
VBA.Month(dtEnd_Date) - VBA.Month(dtStart_Date) - minusnumber
number2 = 12
DATEDIFFERENCE_YM = number1 - (number2 * VBA.Int(number1 / number2))
Exit Function
ErrorHandler:
DATEDIFFERENCE_YM = -1
End Function
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext