CODEWITHSUNDEEP
ms-accessdatediffdate-difference
Muhammad Agdha
Muhammad Agdha

Reputation: 3

Microsoft Access displays both months and years differences

How to display both years and months differences in access 2013. I've been using DateDiff formula, but it didn't work. It seems DateDiff only able to display one of the symbols (m, yyyy, or d). If DateDiff can't perform to display both months and years, is there are other codes available to do that?

Upvotes: 0

Views: 1533

Answers (3)

vbenthu
vbenthu

Reputation: 37

Try this - Years and Month:

DateDiff("yyyy",[Start_Date],Date()) & " Years and " & DateDiff("m",[Start_Date],Date()) Mod 12 & " months"

Upvotes: 0

Gustav
Gustav

Reputation: 55981

You can't do this that simple because DateDiff shows difference between calendar years or months, not a timespan. The above attempt will return this sequence like this:

d1: 2016-02  d2: 2016-08  Y: 0  M: 6 
d1: 2016-02  d2: 2016-09  Y: 0  M: 7 
d1: 2016-02  d2: 2016-10  Y: 0  M: 8 
d1: 2016-02  d2: 2016-11  Y: 0  M: 9 
d1: 2016-02  d2: 2016-12  Y: 0  M: 10 
d1: 2016-02  d2: 2017-01  Y: 1  M: 11 
d1: 2016-02  d2: 2017-02  Y: 1  M: 0 
d1: 2016-02  d2: 2017-03  Y: 1  M: 1 
d1: 2016-02  d2: 2017-04  Y: 1  M: 2 
d1: 2016-02  d2: 2017-05  Y: 1  M: 3 
d1: 2016-02  d2: 2017-06  Y: 1  M: 4

For a true timespan, you will have to use a function like this:

Public Function YearsMonthsDays( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByRef lngYears As Long, _
  Optional ByRef lngMonths As Long, _
  Optional ByRef lngDays As Long) _
  As String

' Returns the difference in years, months, and days between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Gustav Brock, Cactus Data ApS.
' 2010-03-30.

  ' Count of months in a calendar year.
  Const cintMonths  As Integer = 12

  Dim datDateMonth  As Date
  Dim intDays       As Integer

  ' No special error handling.
  On Error Resume Next

  lngMonths = Months(datDate1, datDate2)

  datDateMonth = DateAdd("m", lngMonths, datDate1)
  lngDays = DateDiff("d", datDateMonth, datDate2)
  intDays = Sgn(lngDays)
  If intDays <> 0 Then
    If intDays <> Sgn(DateDiff("d", datDate1, datDate2)) Then
      lngDays = 0
    End If
  End If

  lngYears = lngMonths \ cintMonths
  lngMonths = lngMonths Mod cintMonths

  YearsMonthsDays = CStr(lngYears) & " year(s), " & CStr(lngMonths) & " month(s), " & CStr(lngDays) & " day(s)"

End Function

which uses this helper function:

Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer

  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If

  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff

End Function

Upvotes: 1

Anthony Griggs
Anthony Griggs

Reputation: 1641

Only ran a few tests... I would clean it up a little more to account for the plurals when not needed but this should give you a good start:

   ' Calculate Years and Months Difference
    Private Sub btnCalculate_Click()
        Dim intYears As Integer
        Dim intMonths As Integer
        intYears = (DateDiff("m", Me.txtDateStart, Me.txtDateEnd) / 12)
        intMonths = (DateDiff("m", Me.txtDateStart, Me.txtDateEnd) Mod 12)
        Me.lblDifference.Caption = intYears & " Years " & intMonths & " Months"
    End Sub

ADDITION ON TO NEW EXPLANATION: I haven't tested it but in a query you should be able to do it the same way: Put these expressions in your column headers

TotalYears:=(DateDiff("m", [dtStartDate], [dtEndDate]) / 12)

In another Column Header:

TotalMonths:=TotalYears:=(DateDiff("m", [dtStartDate], [dtEndDate]) Mod 12)

I got it to work just fine. Here is how I applied it: enter image description here

And then you can see the results: enter image description here

Upvotes: 0

Related Questions