How do you Calculate Exact age in Years AND Months

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

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
 
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!
Click to expand...

Kevin, take a look at this...

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


- Jim
 
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.
 
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
Click to expand...


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

K
 
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
 
Hi Kevin

I believe that the following function will give you the proper answer:
Click to expand...

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