Calculate and display age from Birthdate

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
Dave Bradshaw said:
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.
Click to expand...
Click to expand...
 
Or even:

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

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


Dave Bradshaw said:
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.
Click to expand...
Click to expand...
 
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.


Dave Bradshaw said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top