Date differences in MS Access

LillyC
LillyC used Ask the Experts™
on
How to calculate the number of years and months between [Start Date] and [End Date] within a query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Commented:
You can use DATEDIFF  to calculate the difference between 2 dates.
Please check this thread. A similar thread.
https://www.experts-exchange.com/questions/27601269/Access-2007-How-to-calculate-YEARS-of-Service-in-company.html
LillyCDatabase Developer

Author

Commented:
Thanks for your help but I'm trying to find a solution via a query not by coding if possible.
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can use DateDiff in a query:

SELECT DateDiff("m", DateField1, DateField2) AS DateDifference FROM SomeTable

That would give you the number of Months between the two. You can divide by 12 to get the number of Years, and then do some subtraction to get any remainder months:

SELECT (DateDiff("m", DateField1, DateField2))\12 AS YearDiff, (((DateDiff("m", DateField1, DateField2))/12) -  ((DateDiff("m", DateField1, DateField2))\12)) AS MonthDiff FROM SomeTable

You may also be able to use the MOD function to get the Months, but that could perform rounding (which you wouldn't want).

DateDiff: https://support.office.com/en-us/article/datediff-function-e6dd7ee6-3d01-4531-905c-e24fc238f85f
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
keep in mind that the difference between 12/31/17 and 1/1/18 is one month:

?datediff("m", #12/31/17#, #1/1/18#)

so as long as you are good with that then Scott's answer would be a good start.

Another method would be to create a function you could call from you query and do the processing there.

You could pass in the start and end date, then keep adding a month to the start date and count the number of increments until the

DateAdd("m", intLoop, StartDate) > EndDate

Then compute years and months as Scott describes above.

You could also try:

DateDiff("m", StartDate, EndDate) + (Day(EndDate) < Day(StartDate))

which would subtract 1 from the number of months if the day of the month of the EndDate is less than the day of the month of the start date.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I'm trying to find a solution via a query not by coding if possible.

You can't. You will have to use a function like this to get it right. And, after all, it's nothing more than a copy-n-paste into a module:

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

Open in new window

That will returns the exact full count of months. To obtain years and months, do:

TotalMonths = 37  ' example
Years = TotalMonths \ 12
Months = TotalMonths Mod 12

Open in new window

Thus, your query could look like:

Select *, CStr(Months([Start Date], [End Date]) \ 12) & " years, " & CStr(Months([Start Date], [End Date]) Mod 12) & " months" As YearMonth
From YourTable

Open in new window

Top Expert 2014

Commented:
This problem is also related to the age calculation problem.  If you are born on a date (DOB), what is your age, in years, on a later date?  I normally code a VBA function for this.

@LillyC
What do you need the year_count/month_count answer to be for the following:
1. 12/31/17, 1/1/18  - Dale's example
2. 12/15/17, 1/14/18  
3. 12/15/17, 1/15/18  
4. 12/15/17, 1/16/18  
5. 12/15/17, 2/14/19
6. 12/15/17, 2/15/19
7. 12/15/17, 2/16/19
Hamed NasrRetired IT Professional

Commented:
upload a sample database.
Include a table with a few records.
Include a table showing manually the required output.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The results for those dates will be:

1: 0 years, 0 months
2: 0 years, 0 months
3: 0 years, 1 months
4: 0 years, 1 months
5: 0 years, 1 months
5: 0 years, 2 months
6: 0 years, 2 months

Open in new window

Top Expert 2014

Commented:
Gustav

I think you should double-check those results :-)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Ah, missed the 19:

1: 0 years, 0 months
2: 0 years, 0 months
3: 0 years, 1 months
4: 0 years, 1 months
5: 1 years, 1 months
5: 1 years, 2 months
6: 1 years, 2 months

Open in new window

Thanks!
LillyCDatabase Developer

Author

Commented:
Thanks Scott your solution works well for me. Thanks everyone else for you input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial