DATEUNITS

Returns the number of units between two dates.
These replace the built-in function DATEDIF that is no longer supported.
For an illustrated example of the built-in DATEDIF Function refer to the page under Advanced Functions.


DATEUNITS_D

Returns the number of whole days between "start_date" and "end_date".

Public Function DATEUNITS_D( _ 
   StartDate As Date, _
   EndDate As Date) As Long
   
   On Error GoTo ErrorHandler
   Application.Volatile

   DATEUNITS_D = VBA.Int(EndDate - StartDate)
   Exit Function
   
ErrorHandler:
   DATEUNITS_D = -1
End Function

DATEUNITS_M

Returns the number of whole months (or months and days) (or months, weeks and days) between "start_date" and "end_date".

Public Function DATEUNITS_M( _ 
   StartDate As Date, _
   EndDate 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(StartDate, 0)
   eom_end = Application.WorksheetFunction.EoMonth(EndDate, 0)
   
   number1 = (12 * (VBA.Year(EndDate) - VBA.Year(StartDate))) - 12 + _
      (12 - VBA.Month(StartDate)) + VBA.Month(EndDate) - 1

   number2 = (eom_start - StartDate + 1) / VBA.Day(eom_start)
   
   number3 = (1 - (eom_end - EndDate) / VBA.Day(eom_end))
      
   DATEUNITS_M = VBA.Int(VBA.Round(number1 + number2 + number3, 1))
   
   Exit Function
   
ErrorHandler:
   DATEUNITS_M = -1
End Function

DATEUNITS_Y

Returns the number of whole years (or years and days) (or years, months and days) between "start_date" and "end_date".

Public Function DATEUNITS_Y( _ 
   StartDate As Date, _
   EndDate As Date) As Long
         
Dim minusnumber As Integer

   On Error GoTo ErrorHandler
   Application.Volatile
      
   If (VBA.Month(StartDate) > VBA.Month(EndDate)) Then
      minusnumber = 1
   Else
      If (VBA.Month(EndDate) = VBA.Month(StartDate)) Then
         If (VBA.Day(EndDate) < VBA.Day(StartDate)) Then
            minusnumber = 1
         Else
            minusnumber = 0
         End If
      Else
         minusnumber = 0
      End If
   End If
   
   DATEUNITS_Y = VBA.Year(EndDate) - VBA.Year(StartDate) - minusnumber
   
   Exit Function
   
ErrorHandler:
   DATEUNITS_Y = -1
End Function

DATEUNITS_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 DATEUNITS_MD( _ 
   StartDate As Date, _
   EndDate 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(StartDate)) Then
      DATEUNITS_MD = 0
   Else
      If (VBA.Day(EndDate) > VBA.Day(StartDate)) Then
         DATEUNITS_MD = VBA.Day(EndDate) - VBA.Day(StartDate)
      Else
         number1 = VBA.DateSerial(VBA.Year(StartDate), _
                                  VBA.Month(StartDate) + 1, _
                                  VBA.Day(EndDate))
         DATEUNITS_MD = number1 - StartDate
      End If
   End If
      
   Exit Function
   
ErrorHandler:
   DATEUNITS_MD = -1
End Function

DATEUNITS_YD

Returns the number of days between "start_date" and "end_date" (ignoring years).

Public Function DATEUNITS_YD( _ 
   dtStart_Date As Date, _
   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
         DATEUNITS_YD = 0
      Else
         If (start_day <= end_day) Then
            lastdate = VBA.DateSerial(start_year, end_month, end_day)
            firstdate = VBA.DateSerial(start_year, start_month, start_day)
            DATEUNITS_YD = lastdate - firstdate
         Else
            If (start_year < (end_year - 1)) Then
               lastdate = VBA.DateSerial(start_year + 1, end_month, end_day)
               firstdate = VBA.DateSerial(start_year, start_month, start_day)
               DATEUNITS_YD = lastdate - firstdate
            Else
               lastdate = VBA.DateSerial(start_year, end_month, end_day)
               firstdate = VBA.DateSerial(start_year, start_month, start_day)
               DATEUNITS_YD = lastdate - firstdate
            End If
         End If
      End If

   Else
      If (start_month < end_month) Then
         lastdate = VBA.DateSerial(start_year, end_month, end_day)
         firstdate = VBA.DateSerial(start_year, start_month, start_day)
         DATEUNITS_YD = lastdate - firstdate
      Else
         lastdate = VBA.DateSerial(start_year + 1, end_month, end_day)
         firstdate = VBA.DateSerial(start_year, start_month, start_day)
         DATEUNITS_YD = lastdate - firstdate
      End If
   End If

   Exit Function
   
ErrorHandler:
   DATEUNITS_YD = -1
End Function

DATEUNITS_YM

Returns the number of months between "start_date" and "end_date" (ignoring days and years).

Public Function DATEUNITS_YM( _ 
   StartDate As Date, _
   EndDate 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(StartDate)) Then
      minusnumber = 1
   End If
   
   number1 = (12 * (VBA.Year(EndDate) - VBA.Year(StartDate))) + _
      VBA.Month(EndDate) - VBA.Month(StartDate) - minusnumber
   
   number2 = 12
      
   DATEUNITS_YM = number1 - (number2 * VBA.Int(number1 / number2))
   
   Exit Function
   
ErrorHandler:
   DATEUNITS_YM = -1
End Function

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext