426,107 Members | 1,323 Online 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

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.

7 Replies

 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

 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 Dim dblYears As Double Dim intYears As Integer Dim dblMonths As Double Dim intMonths As Integer Dim intDays As Integer   intDays = DateDiff("d", Me.[Birth_Date], Date) dblYears = intDays / 365.25 intYears = Fix(dblYears) dblMonths = (dblYears - intYears) * 12 intMonths = Fix(dblMonths) intDays = ((dblMonths - intMonths) / 12) * 365   Me.txtResult = intYears & " years, " & intMonths & " months, " & intDays & " days"   Feb 16 '17 #4

 Expert 100+ P: 1,107 You also might find these useful: Age() Function Expand|Select|Wrap|Line Numbers Public Function getAge(ByVal vDateOfBirth As Variant, ByVal vDateReference As Variant) As String       Dim iDays As Integer     Dim iMonths As Integer     Dim iYears As Integer     Dim iDaysInMonth As Integer     Dim dDateOfBirth As Date     Dim dDateReference As Date       dDateOfBirth = Int(Nz(vDateOfBirth, Now()))     dDateReference = Int(Nz(vDateReference, Now()))       iDaysInMonth = DateSerial(Year(dDateOfBirth), Month(dDateOfBirth) + 1, 1) - DateSerial(Year(dDateOfBirth), Month(dDateOfBirth), 1)       iYears = DatePart("yyyy", dDateReference) - DatePart("yyyy", dDateOfBirth)     iMonths = DatePart("m", dDateReference) - DatePart("m", dDateOfBirth)     iDays = DatePart("d", dDateReference) - DatePart("d", dDateOfBirth)     If iDays < 0 Then         iMonths = iMonths - 1         iDays = iDaysInMonth + iDays     End If     If iMonths < 0 Then         iYears = iYears - 1         iMonths = 12 + iMonths     End If       getAge = iYears & " yr(s).  " & iMonths & " mo(s).  " & iDays & " day(s)" 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

 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 