How do you Calculate Exact age in Years AND Months

[Bayou BoB]

In my Client Query, I'd like to calculate exact age of the client...in
Years, and months. I'm wondering what expression I might use to do
this? It would be extremely beneficial to have the calculation to
display in a number of our forms, and I have a workable version to get
age in years, and am using this now:

=DateDiff("yyyy",[DateOfBirth],Date())+(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"))

I'm wondering how that should be modified to give me a result of say
34.6, meaning 34 years, 6 months old. Many thanks!

Kevin

 

[Jim Allensworth]

In my Client Query, I'd like to calculate exact age of the client...in
Years, and months. I'm wondering what expression I might use to do
this? It would be extremely beneficial to have the calculation to
display in a number of our forms, and I have a workable version to get
age in years, and am using this now:

=DateDiff("yyyy",[DateOfBirth],Date())+(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"))

I'm wondering how that should be modified to give me a result of say
34.6, meaning 34 years, 6 months old. Many thanks!

Kevin, take a look at this...

http://www.mvps.org/access/datetime/date0001.htm


- Jim

 

[Van T. Dinh]

Beware that months are more or less duodecimal (based on 12) with respect to
years and you are trying to represent it with a decimal number. For
example, 34 years 11 months will be shown as 34.11 which is less than 34.2
(34 years 2 months).

It can be done as a Text display but it will be very confusing for your
users and I would certainly avoid this kind of representations.

 

[Douglas J. Steele]

See whether "A More Complete DateDiff Function", at
http://members.rogers.com/douglas.j.steele/Diff2Dates.html, helps.

While it's going to return 34 years, 6 months, not 34.6, hopefully you can
see how to modify the function to do what you want. (And take Van's advice
to heart. Don't show that as 34.6: use 34.01, 34.02, 34.02, up to 34.12)

 

[Bayou BoB]

In my Client Query, I'd like to calculate exact age of the client...in
Years, and months. I'm wondering what expression I might use to do
this? It would be extremely beneficial to have the calculation to
display in a number of our forms, and I have a workable version to get
age in years, and am using this now:

=DateDiff("yyyy",[DateOfBirth],Date())+(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"))

I'm wondering how that should be modified to give me a result of say
34.6, meaning 34 years, 6 months old. Many thanks!

Kevin

Thanks! I'll have a look at the links provided here!

K

 

[StCyrM]

Hi Kevin

I believe that the following function will give you the proper answer:

Best regards

Maurice St-Cyr




Option Compare Database
Option Explicit

Function AgeInYears(BirthDate As Date, Optional AsOfDate As Date = 0) As Double
'------------------------------------
'Purpose: Get Age in years with a given birthdate
'Returns: Age in Years
'Arguments: BirthDate
' AsOfDate - if specified will calculate age on that date
' if not specified Today's date will be used
'Uses: IsLeapYear()
'Notes: None
'Revised: 2/21/2000
'------------------------------------
Dim intAgeYear As Integer
Dim intAgeDays As Integer
Dim dblAge As Double
Dim intDivisor As Integer
If AsOfDate = 0 Then AsOfDate = VBA.date
intAgeYear = DateDiff("yyyy", BirthDate, AsOfDate)
intAgeDays = DateDiff("d", DateSerial(Year(AsOfDate), Month(BirthDate),
Day(BirthDate)), AsOfDate)
'set divisor for determing part of year
intDivisor = 365
If IsLeapYear(Year(AsOfDate)) = True Then intDivisor = 366
If intAgeDays <> 0 Then
AgeInYears = intAgeYear + (intAgeDays / intDivisor)
Else
AgeInYears = intAgeYear
End If
End Function

Function IsLeapYear(YearNumber As Integer) As Boolean
IsLeapYear = (Day(DateSerial(YearNumber, 2, 28) + 1) = 29)
End Function

 

[John Vinson]

Hi Kevin

I believe that the following function will give you the proper answer:

ummmm... it would if the poster wanted the age in years; the specific
request was for age in years AND MONTHS.