Calculate and display age from Birthdate

[Guest]

Hi
I have a table of employees. It contains a field for date of birth. I would
like a field next to it that will calculate and display the age from the
entered date of birth. Is there a built in function that could do this or a
piece of code needed?
If so could someone kindly tell me how?
Thanks
Kim

 

[Mark Phillipson]

Hi,

You can use the DateDiff function as follows:

DateDiff("yyyy",[DOB],Date())

This will return the age.

DOB is the name of the Date of Birth field.
--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/

 

[Dave Bradshaw]

Unfortunately DateDiff will not work as it gives the number of years
between the two dates, including the birthday year, and the year now,
so it will give an age one year older than you are in any year, until
you have passed your birthday

Here is something that I have used in a text box on a form:

=IIf(DateAdd("yyyy",DateDiff("yyyy",[BirthDate],Now()),[BirthDate])<Now(),0,-1)+DateDiff("yyyy",[BirthDate],Now())

It looks clumsy (I wrote it some time ago), and someone may have a
more elegant way of doing it, maybe with a user-defined function, but
it does work.

 

[Ron Weiner]

Here is a user defined function that re turns the persons age in Years and
Months (21y-2m)

Public Function GetAgeStr(varDOB As Variant, varDate As Variant) As String
' Purpose Returns a string of the age between dates in the following
format:
' Yy-Mm where Y is years and M is the months between dates
Dim dteDOB As Date, dteDate As Date
Dim lngMonths As Long, lngYears As Long
If IsDate(varDOB) And IsDate(varDate) Then
dteDOB = CDate(varDOB)
dteDate = CDate(varDate)
lngMonths = DateDiff("M", dteDOB, dteDate) Mod 12
lngYears = DateDiff("M", dteDOB, dteDate) \ 12
If DatePart("m", dteDate) = DatePart("m", dteDOB) And DatePart("d",
dteDate) < DatePart("d", dteDOB) Then
lngYears = lngYears - 1
lngMonths = 11
End If
GetAgeStr = lngYears & "y-" & lngMonths & "m"
Else
GetAgeStr = ""
End If
End Function

Ron W

Unfortunately DateDiff will not work as it gives the number of years
between the two dates, including the birthday year, and the year now,
so it will give an age one year older than you are in any year, until
you have passed your birthday

Here is something that I have used in a text box on a form:

=IIf(DateAdd("yyyy",DateDiff("yyyy",[BirthDate],Now()),[BirthDate])<Now(),0,
-1)+DateDiff("yyyy",[BirthDate],Now())

It looks clumsy (I wrote it some time ago), and someone may have a
more elegant way of doing it, maybe with a user-defined function, but
it does work.

Hi,

You can use the DateDiff function as follows:

DateDiff("yyyy",[DOB],Date())

This will return the age.

DOB is the name of the Date of Birth field.

 

[Mark Phillipson]

Or even:

Int(DateDiff("m",[DOB],Date())/12)

This should (I think) show as 20 the month before a 21st birthday.

Unfortunately DateDiff will not work as it gives the number of years
between the two dates, including the birthday year, and the year now,
so it will give an age one year older than you are in any year, until
you have passed your birthday

Here is something that I have used in a text box on a form:

=IIf(DateAdd("yyyy",DateDiff("yyyy",[BirthDate],Now()),[BirthDate])<Now(),0,-1)+DateDiff("yyyy",[BirthDate],Now())

It looks clumsy (I wrote it some time ago), and someone may have a
more elegant way of doing it, maybe with a user-defined function, but
it does work.

Hi,

You can use the DateDiff function as follows:

DateDiff("yyyy",[DOB],Date())

This will return the age.

DOB is the name of the Date of Birth field.

 

[Dave Bradshaw]

This links to the MSDN website to a page all about calculating ages,
They say even calculating the age in days and dividing by 365.25 will
occasionally be wrong. They do give another function for calculating
the age.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/calculatingage.asp

Or even:

Int(DateDiff("m",[DOB],Date())/12)

This should (I think) show as 20 the month before a 21st birthday.

Unfortunately DateDiff will not work as it gives the number of years
between the two dates, including the birthday year, and the year now,
so it will give an age one year older than you are in any year, until
you have passed your birthday

Here is something that I have used in a text box on a form:

=IIf(DateAdd("yyyy",DateDiff("yyyy",[BirthDate],Now()),[BirthDate])<Now(),0,-1)+DateDiff("yyyy",[BirthDate],Now())

It looks clumsy (I wrote it some time ago), and someone may have a
more elegant way of doing it, maybe with a user-defined function, but
it does work.

Hi,

You can use the DateDiff function as follows:

DateDiff("yyyy",[DOB],Date())

This will return the age.

DOB is the name of the Date of Birth field.

 

[Dirk Goldgar]

This links to the MSDN website to a page all about calculating ages,
They say even calculating the age in days and dividing by 365.25 will
occasionally be wrong. They do give another function for calculating
the age.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/calculatingage.asp

And just to add another variation into the mix, there are the functions
posted here:

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