# Date differences in MS Access

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® is a registered trademark of EXPERTS EXCHANGE®
EE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

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

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
Owner, 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
``````
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
``````
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
``````
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
Retired 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
``````
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
``````
Thanks!
Database Developer

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

Do more with

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