By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Using DATEDIFF to calculate age in years, months, and days

P: 6
I know it's been asked and answered, but I'm still not following. I'm an accountant using MS Access to create an Human Resources database. I am looking for the DATEDIFF formula to calculate the age of a person in Years, Months, and Days. I've copied and pasted formulas I've found on the net and am NOT getting the right results, though the format (Y-M-D) is correct. For example, I am 46 years, 5 months, and 1 day old, yet the answer I get is 47 years, 7 months, and 199 days.

Here is the formula:

=DateDiff("yyyy",[Birth Date],Date()) & " yr(s). " & DateDiff("m",[Birth Date],Now())-(DateDiff("yyyy",[Birth Date],Now())*12) & " mo(s)." & DateDiff("d",[Birth Date],Now())-(DateDiff("yyyy",[Birth Date],Now())*365) & " day(s)"

Any help (like actually writing the formula so I can copy and paste) would be greatly appreciated. Thanks.
Feb 16 '17 #1

✓ answered by gnawoncents

You would add the jforbes code to your VBA script on the applicable form, then call it using the getAge([Birth_Date]) command.

My code could be added to VBA using an event (button on_click or a field after_update, for example.

Share this Question
Share on Google+
7 Replies


gnawoncents
100+
P: 214
The problem is parsing out the individual fields. You need to do one DateDiff for the whole, then get the parts. I imagine the 7 months and 199 days might include a negative sign on one or both (indicating it is 47 years, minus 7 months and/or 199 days. This would happen if you parse out months and get January(1) - August(8) = 7.
Feb 16 '17 #2

P: 6
You are correct... 47 yrs. -7 mos. -199 days.
Feb 16 '17 #3

gnawoncents
100+
P: 214
No result is going to be exact, since Access doesn't have a built-in feature for this, but the code below should get you within +/- 1 day.

Expand|Select|Wrap|Line Numbers
  1. Dim dblYears As Double
  2. Dim intYears As Integer
  3. Dim dblMonths As Double
  4. Dim intMonths As Integer
  5. Dim intDays As Integer
  6.  
  7. intDays = DateDiff("d", Me.[Birth_Date], Date)
  8. dblYears = intDays / 365.25
  9. intYears = Fix(dblYears)
  10. dblMonths = (dblYears - intYears) * 12
  11. intMonths = Fix(dblMonths)
  12. intDays = ((dblMonths - intMonths) / 12) * 365
  13.  
  14. Me.txtResult = intYears & " years, " & intMonths & " months, " & intDays & " days"
  15.  
Feb 16 '17 #4

jforbes
Expert 100+
P: 1,107
You also might find these useful:

Age() Function

Expand|Select|Wrap|Line Numbers
  1. Public Function getAge(ByVal vDateOfBirth As Variant, ByVal vDateReference As Variant) As String
  2.  
  3.     Dim iDays As Integer
  4.     Dim iMonths As Integer
  5.     Dim iYears As Integer
  6.     Dim iDaysInMonth As Integer
  7.     Dim dDateOfBirth As Date
  8.     Dim dDateReference As Date
  9.  
  10.     dDateOfBirth = Int(Nz(vDateOfBirth, Now()))
  11.     dDateReference = Int(Nz(vDateReference, Now()))
  12.  
  13.     iDaysInMonth = DateSerial(Year(dDateOfBirth), Month(dDateOfBirth) + 1, 1) - DateSerial(Year(dDateOfBirth), Month(dDateOfBirth), 1)
  14.  
  15.     iYears = DatePart("yyyy", dDateReference) - DatePart("yyyy", dDateOfBirth)
  16.     iMonths = DatePart("m", dDateReference) - DatePart("m", dDateOfBirth)
  17.     iDays = DatePart("d", dDateReference) - DatePart("d", dDateOfBirth)
  18.     If iDays < 0 Then
  19.         iMonths = iMonths - 1
  20.         iDays = iDaysInMonth + iDays
  21.     End If
  22.     If iMonths < 0 Then
  23.         iYears = iYears - 1
  24.         iMonths = 12 + iMonths
  25.     End If
  26.  
  27.     getAge = iYears & " yr(s).  " & iMonths & " mo(s).  " & iDays & " day(s)"
  28. End Function
Feb 16 '17 #5

P: 6
I'm assuming the above is VBA code. If so, where do I copy and paste that? Not sure how this all works. Thanks.
Feb 16 '17 #6

gnawoncents
100+
P: 214
You would add the jforbes code to your VBA script on the applicable form, then call it using the getAge([Birth_Date]) command.

My code could be added to VBA using an event (button on_click or a field after_update, for example.
Feb 16 '17 #7

P: 6
It worked! Thanks so much for your help.
Feb 17 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.