Subtracting Dates, getting whole months

Rats

Registered User.
Local time
Today, 19:42
Joined
Jan 11, 2005
Messages
151
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.

Code:
Option Compare Database
Option Explicit

Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
  On Error GoTo Err_Handler
  
  If IsNull(StartDate) Then
     getTimeElapsed = "No startdate"
     Exit Function
  End If
  
  Dim YY As Integer
  Dim MM As Integer
  Dim DD As Long
  
  ' use current day if EndDate is not supplied,
  ' + 1 makes both days inclusive.
  EndDate = IIf(EndDate = 0, Date, EndDate) + 1
   
  MM = DateDiff("m", StartDate, EndDate)
  DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
  
  ' recalculate if DD is negative.
  If DD < 0 Then
    MM = MM - 1
    DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
  End If
  
  YY = MM \ 12       ' integer division.
  MM = MM Mod 12     ' remainder.
  
  getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
                   MM & IIf(MM < 2, " month ", " months ") & _
                   DD & IIf(DD < 2, " day", " days")
  Exit Function
  
Err_Handler:
  MsgBox Err.Description
  Exit Function

End Function
 
Found answer thanks

Found that by simply modifying the DateDiff function I could obtain my answer.
Code:
MonthsElapsed: DateDiff("m",[customers]![startmonth],[startdate])

Works a treat.
 

Users who are viewing this thread

Back
Top Bottom