# How do I calculate age in years?

I feel really stupid asking this but cannot get the formatting or expression correct.
I  have today's date, DATE(), and I have the horses' date of birth, datHorseDateOfBirth, so I use the formula = Date()-datHorseDateOfBirth but I get big numbers.  I just want integers, rounded up.
###### Who is Participating?

CIOCommented:
OK, that's clear and simple. Then, in your query:

AgeHorse: DateDiff("yyyy",[datDateOfBirth],Date())+1

/gustav
0

Commented:
(Date()-([datHorseDateOfBirth])/365.25
0

Commented:
Also, make sure to formate the field to show the number of decimals you want (I would choose two).

0

Commented:
SELECT
Int(DateDiff("m",[datHorseDateOfBirth],Date()))+(Date()<DateSerial(Year(Date()),Month(Date()),Day([datHorseDateOfBirth]))) AS _Months
, Int([_Months]/12) AS Age_Years
, [_Months] Mod 12 AS Age_Months
FROM
Table1;
0

Commented:
You could just use Round() on Age_Years.
0

Commented:
Rick is probably closest to the quick and easy (and most accurate).  Try:

DateDiff("yyyy", [DOB], Date()) + (Format([DOB], "mmyy") > Format(Date(), "mmyy"))
0

President / OwnerCommented:
Use the attached function.  Paste into a module and then call with DOB and a date "as of".

JimD.
Function GetAge1(varBD As Variant, Optional varAsOfDate As Variant = Null) As Integer

Dim intAge As Integer

10      If Nz(varBD, "") = "" Then varBD = Now()
20      If Nz(varAsOfDate, "") = "" Then varAsOfDate = Now()

30      GetAge1 = DateDiff("yyyy", varBD, varAsOfDate) + (Format(varAsOfDate, "mmdd") < Format(varBD, "mmdd"))

End Function
0

Commented:
If we know Birth date of HORSES, you can calculate age using function DateDiff("yyyy",DateOfBirth,Date()). But this simple formula will not always give us correct results. If Birth day did not passed yet in the current year, we will receive Age bigger by 1. In order to overcome this trouble, we need to improve the formula. Can do this by substructing 1 year from the result if Birth day did not take place yet. Current year Birth date can be constructed in this manner:

DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))

Expression:

Date() < DateSerial(Year( Date()),Month(DateOfBirth), Day(DateOfBirth)))

will return (-1) if Current Date is smaller than the Birth Date of this year.

I applied query on EMPLOYEE table you can apply this on your desire table ......

SELECT FirstName, LastName, DateOfBirth,
DateDiff ("yyyy",DateOfBirth, Date())+(Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS Age,
Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS AddYear
FROM Employees;

Hope this will help you to solve the problem.

Regards,

Asif Ahmed Khan
0

Commented:
Jim's function has the added benefit of accepting VARIANT (read NULL) values, which I have found to be a significant problem with databases containing DOB fields.  However, if I were going to go the route of a function, I would probably change the functions data type to Variant, and return either a NULL or a text string "Unknown" if either of the dates passed is NULL.
0

Author Commented:
KGNickl: I wrote: Age: Format((Date()-([datDateOfBirth]))/365.25,0)

Rick: In the field box of the QBE I typed Age: SELECT   Int(DateDiff("m",[datDateOfBirth],Date()))+(Date()<DateSerial(Year(Date()),Month(Date()),Day([datDateOfBirth]))) AS _Months   , Int([_Months]/12) AS Age_Years   , [_Months] Mod 12 AS Age_Months FROM tblHorses
and got a "syntax error in subquery" message

fyed: I typed Age: SELECT  IAge: DateDiff("yyyy", [datDateOfBirth], Date()) + (Format([datDateOfBirth], "mmyy") > Format(Date(), "mmyy"))  , Int([_Months]/12) AS Age_Years  , [_Months] Mod 12 AS Age_Months FROM tblHorses
and got the age in months.  Could you rewrite the expression so it gives me the age, for example, as "1 yr 3 mos"?

Kahn: I typed: AGE: SELECT [datDateOfBirth],
DateDiff ("yyyy",[datDateOfBirth], Date())+(Date() < DateSerial(Year( Date()), Month([datDateOfBirth]), Day([datDateOfBirth]))) AS Age,
Date() < DateSerial(Year( Date()), Month([datDateOfBirth]), Day([datDateOfBirth]))) AS AddYear
FROM tblHorses
but the QBE would not accept it and reverted to my original Date()-datDateOfBirth

I prefer not to use modules as I am working with the report's RecordSource as a query.
0

President / OwnerCommented:
<<I prefer not to use modules as I am working with the report's RecordSource as a query. >>

Queries can call user defined functions (UDF).  Only trick is that you must reference a field so that it gets called for each row.

<<  Could you rewrite the expression so it gives me the age, for example, as "1 yr 3 mos"?>>

Your probably going to end up with a UDF.  The amount of logic that you'll need to return the months is not something your going to be able to easily do in a SQL statement.

JimD.

0

CIOCommented:
> Rick is probably closest to the quick and easy (and most accurate).

No, at least not the most accurate - and if not, what is it worth?

First:
> How do I calculate age in years? .. I just want integers, rounded up.
Next:
> Could you rewrite the expression so it gives me the age,
> for example, as "1 yr 3 mos"?

You have to make up your mind. Should it be age in years and months or just years? And should it be "rounded up" - which doesn't return an age but a period as an age always is rounded down.

/gustav
0

Commented:
>   Rick: In the field box of the QBE I typed Age: SELECT   Int(DateDiff("m",[datDateOfBirth],Date()))+
>   (Date()<DateSerial(Year(Date()),Month(Date()),Day([datDateOfBirth]))) AS _Months   ,
>   Int([_Months]/12) AS Age_Years   , [_Months] Mod 12 AS Age_Months FROM tblHorses
>   and got a "syntax error in subquery" message

That was just to give you an idea.
You need to divide _Months by 12 and apply the Rounding. Not use what I typed.
0

Director of TrainingCommented:
MS has a very nice sample DB that will show you exactly how to accomplish this.  It include the creation of a module that contains two functions Age() and AgeMonths().  Allowing to you show an age in two different formats.  I suggest you take a look at it and see if it helps you gather an understanding of what is bing done and how. You can download the Access 2007 version here.

http://office.microsoft.com/en-us/templates/results.aspx?qu=calculating+age&origin=CT010117248#ai:TC010288085|

I have attached the functions that are in the module if you just want to use them from here.  Just note, I did not write them, they are directly from the MS sample DB.

Hope it helps.

~:TLH:~
Option Compare Database
Option Explicit

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
'    Calculates age in years from a specified date to today's date.
'
' INPUT PARAMETERS:
'    varBirthDate:  a birth date.
'
' RETURN
'    Age in years.
'
'*************************************************************
Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthDate) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

'*************************************************************
' FUNCTION NAME: AgeMonths()
'
' PURPOSE:
'  Compliments the Age() function by calculating the number of months
'  that have expired since the last month supplied by the specified date.
'  If the specified date is a birthday, the function returns the number of
'    months since the last birthday.
'
' INPUT PARAMETERS:
'    varBirthDate:  a birth date.
'
' RETURN
'    Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal varBirthDate As Variant) As Integer

If IsNull(varBirthDate) Then AgeMonths = 0: Exit Function

Dim tAge As Double
tAge = (DateDiff("m", varBirthDate, Now))
If (DatePart("d", varBirthDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function
0

Author Commented:
Thanks to all for the info.

I will stick to the age in years rounded up to the nearest integer because that's how horses are "aged".  In other words, a horse (especially in the Thoroughbred world, and we rescue a lot of them here in the Ocala, FL area) is considered to be one year old if it is born on New Year’s Day.
0

Commented:
Hello Bro!

Thats a very good work and best of luck for your future and God bless you in every field of life.

Regards,

Asif Ahmed Khan
0

CIOCommented:
> MS has a very nice sample DB that will show you exactly
> how to accomplish this.

No, these functions are just examples showing how not to do it as they don't take leap years into account.

> I will stick to the age in years rounded up to the nearest integer ..

This can be done this way:
Public Function AgeHorse( _
ByVal datDateOfBirth As Date) _
As Integer

' Returns the difference in years from datDateOfBirth to current date
' rounded up.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2011-03-23. Cactus Data ApS, CPH.

Dim datToday  As Date
Dim intAge    As Integer
Dim intYears  As Integer

datToday = #2/29/2008# 'Date
' Find difference in calendar years.
intYears = DateDiff("yyyy", datDateOfBirth, datToday)
If intYears >= 0 Then
' Increase by 1 if current date is equal to or later than birthday of the
' current year using DateDiff to ignore a time portion of datDateOfBirth.
intAge = intYears + Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) <= 0)
End If

AgeHorse = intAge

End Function

> a horse .. is considered to be one year old if it is born on New Year’s Day.

This is again confusing. So how old a horse born on, say, Feb. 1th? Zero years?
And when will these two horses reach an age of 0(?), 1, and 2?

/gustav
0

Author Commented:
Gustav, I know it's confusing but I have to live within the rules if I want to get a grant from Thoroughbred-related charities.  Go to http://www.beautysequinerescue.org/tonka's_task_force.htm if you want to see what we deal with.  Thus, I don't make the rules, the Jockey Club does.
Per Jockey Club rules (http://www.jockeyclub.com/mediaCenter.asp?story=197): "The rule changes appear in Section V of the Rules. Rule 1(C) states: For the purpose of determining Age of the Thoroughbred, the date of birth of a Thoroughbred is deemed to be January 1 of the year of foaling."

For the purposes of my grant application, a horse born (foaled) anytime in 2011 is considered one year old, foaled in 2010 would be two years old, a horse foaled in 2009 is 3 years old, etc.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.