Date differences in MS Access

How to calculate the number of years and months between [Start Date] and [End Date] within a query.
LillyCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MAS (MVE)EE Solution Guide - Technical Dept HeadCommented:
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
0
LillyCAuthor Commented:
Thanks for your help but I'm trying to find a solution via a query not by coding if possible.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeCommented:
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.
1
Gustav BrockCIOCommented:
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

1
aikimarkCommented:
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
0
hnasrCommented:
upload a sample database.
Include a table with a few records.
Include a table showing manually the required output.
0
Gustav BrockCIOCommented:
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

0
aikimarkCommented:
Gustav

I think you should double-check those results :-)
0
Gustav BrockCIOCommented:
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!
0
LillyCAuthor Commented:
Thanks Scott your solution works well for me. Thanks everyone else for you input.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.