Thanks VBSlammer - the formulas worked great

[GaryWilsonCPA]

Thanks VBSLAMMER - You are the man...............

GaryWilsonCPA, if you're a CPA then you're a stickler for accurate numbers, am I right? Well, there has been much debate over how to get an accurate age based on a birthdate. Dividing by 365 works most of the time, but doesn't take into account leap years. If the day and month of the birthdate is within a day or two of the present date, the age will be off.

Generally, you have to use the DateDiff() function in VBA to get true date differences, because using [date1] - [date2] can produce unpredictable results. There are several example functions on the Internet that return the age in different formats; some include the years, months and days.

For a quick and easy solution, I use something like this:
CODE
ControlSource: =Fix(DateDiff("d", [BirthDate], Date()) / 365.245)
Or for a string value like "21 years and 38 days" I use a function like this:
CODE

'*
'*   pass full year in dtBorn: "4/2/1899"
'*
Function GetAgeString(ByVal dtBorn As String) As String
On Error GoTo ErrHandler
  Dim dtToday As String
  Dim dtCompare As String
  Dim intYears As Integer
  Dim intDays As Integer
  
  If DateDiff("d", dtBorn, Date) < 0 Then
    GetAgeString = "In the future"
    GoTo ExitHere
  End If
  
  dtToday = Format(Date, "mm/dd/yyyy")
  intYears = Fix(DateDiff("d", dtBorn, dtToday) / 365.245)
  
  dtCompare = Month(dtBorn) & "/" & Day(dtBorn) & "/"
  
  If Format(dtCompare & Year(Date), "mm/dd/yyyy") > dtToday Then
    intDays = DateDiff("d", dtCompare & (Year(Date)) - 1, dtToday) - 1
  Else
    intDays = DateDiff("d", dtCompare & Year(Date), dtToday)
  End If
  
  GetAgeString = intYears & " years and " & intDays & " days."

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function
Set the ControlSource as:
CODE
ControlSource: =GetAgeString(Format([Birthdate],"mm/dd/yyyy"))
Output if [BirthDate] is "8/21/1959" :
CODE
45 years and 146 days.
I haven't tested these extensively, they might also produce inaccuracies in certain cases - that's why I have a CPA: to balance the books...

OK Hap007, delete thread when ready


VBSlammer

Unemployed in Houston, Texas

 

[MichaelRed]

I really do not mean to be pendantic, but ... please, potential users of the function posted above.

There are numerous threads / posts addressing the issue of "AGE" in these (Tek-Tips) fora. One swhould really look at a fair sampling of these before deciding on one to use. Most have some "boundary conditions" (limits), but (if memory serves correctly) ALL which attempt to use some number to represent the length of a year as a single value (e.g. [365 | 365.25 | 365.245 | ... ] introduce an error at some (relatively near) point. There are alternatives in these fora, which are accurate (exactly) over much larger ranges.





MichaelRed

 

[VBslammer]

Not to be pedantic, but pendantic is spelled pedantic...

VBSlammer

Unemployed in Houston, Texas