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