Calculating age from Date of Birth

K

Kate

Hi
I am doing a userform which asks the user to enter date of birth from
a list box and was wondering if anyone knows the code which will
calculate the age of that person on their last birthday.

Thanks for any help you can offer!
 
M

macropod

Hi Kate,

Two solutions:

1. For a field-based calculation download the Word document at:
www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
(url all one line)

2. For a vba-based solution, try something based on:

Sub CalcAge()
Dim BirthDate As Date
Dim Years As Integer, Months As Integer
Dim Days As Integer, DaysInMonth As Integer
BirthDate = InputBox("Please enter your date of birth.")
If (Month(Date) = 2) Then
DaysInMonth = 28 + (Month(Date) = 2) * ((Year(Date) Mod 4 = 0) + _
(Year(Date) Mod 400 = 0) - (Year(Date) Mod 100 = 0))
Else
DaysInMonth = 31 - (Month(Date) = 4) - (Month(Date) = 6) - _
(Month(Date) = 9) - (Month(Date) = 11)
End If
Years = Year(Date) - Year(BirthDate) + (Month(Date) < Month(BirthDate)) + _
(Month(Date) = Month(BirthDate)) * (Day(Date) < Day(BirthDate))
Months = (12 + Month(Date) - Month(BirthDate) + _
(Day(Date) < Day(BirthDate))) Mod 12
Days = (DaysInMonth + Day(Date) - Day(BirthDate)) Mod DaysInMonth
MsgBox "Your age is " & Years & " years " & Months & " months " & Days & "
days "
End Sub

(not sure how this will wrap - the underscores '_' indicate line
continuations)

Cheers
 
J

Jezebel

Wow! talk about overkill on the VBA solution ...

Macro, I can understand your use of DIY date solutions in fields where
there's no alternative, but in VBA, this is just idiotic. Use DateDiff(),
for heaven's sake.
 
M

Malcolm Smith

Kate

You may have to be very careful about which date structure one uses. I
would have three list boxes, one for each of day, month and year otherwise
if we get a Brit and an American both entering "10/11/1967" then we won't
know if we're coming or going.

- Malc
 
H

Helmut Weber

Hi Kate,
an example from Harald. M. Genauk,
which should work with other date formats as well.
The one here is the German format.
'---
Sub Makro1()
MsgBox CompleteYears("06.11.1949", "05.11.2004")
End Sub
'---
Public Function CompleteYears(ByVal Date1 As Date, Date2 As Date) _
As Integer

Dim nDate1 As Date
Dim nDate2 As Date
Dim nYears As Integer

If Date1 <= Date2 Then
nDate1 = Date1
nDate2 = Date2
Else
nDate1 = Date2
nDate2 = Date1
End If
nYears = DateDiff("yyyy", nDate1, nDate2)
If DateSerial(Year(nDate2), Month(nDate1), Day(nDate1)) _
<= nDate2 Then
CompleteYears = nYears
Else
CompleteYears = nYears - 1
End If
End Function
'---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/
 
M

macropod

As I've said before, vba's datediff doesn't work for calculating a person's
age - at least not without a lot of extra effort. When datediff calculates
the elapsed years, it just does a year number subtraction without regard to
months and days. So:

Sub Test()
MsgBox DateDiff("yyyy", "31 / 12 / 2003", "1 / 1 / 2004")
End Sub

returns '1', for a 1-day difference! Not real clever. At least datedif as a
worksheet function in Excel takes those extra issues into account.

Cheers
 
M

Martin Seelhofer

Hey there
As I've said before, vba's datediff doesn't work for calculating a
person's
age - at least not without a lot of extra effort. When datediff calculates
the elapsed years, it just does a year number subtraction without regard
to
months and days.
Click to expand...

Well, if that's the only problem, where's the "lot of extra effort", then?

Function YearDiff(ByVal d1 As Date, ByVal d2 As Date) As Long
Dim d As Date ' temp var

' force d1 to be <= d2
If d1 > d2 Then
d = d1
d1 = d2
d2 = d
End If

' calculate difference
YearDiff = DateDiff("yyyy", d1, d2)

' pad the result
If Month(d2) < Month(d1) Or _
(Month(d2) = Month(d1) And Day(d2) < Day(d1)) Then
YearDiff = YearDiff - 1
End If
End Function



Cheers,
Martin
 
M

macropod

BTW Martin,

On what date would a person born on 29/2/2004 celebrate their 1st birthday
under your algorithm?

Cheers
 
J

Jean-Guy Marcil

macropod was telling us:
macropod nous racontait que :
As I've said before, vba's datediff doesn't work for calculating a
person's age - at least not without a lot of extra effort. When
datediff calculates the elapsed years, it just does a year number
subtraction without regard to months and days. So:

Sub Test()
MsgBox DateDiff("yyyy", "31 / 12 / 2003", "1 / 1 / 2004")
End Sub

returns '1', for a 1-day difference! Not real clever. At least
datedif as a worksheet function in Excel takes those extra issues
into account.
Click to expand...

How about this, using DateDiff to get a rough estimate? I say "rough"
because the weakness lies in the number of days per moth.
'_______________________________________
Sub RoughDateDiffEstimate()
Dim DayDiff As Long
Dim NumYear As Long
Dim NumMonth As Long
Dim NumDay As Long
Dim CheckNum As Single
Dim Date1 As Date
Dim Date2 As Date

Date1 = "2 / 1 / 2004"
Date2 = "3 / 1 / 2004"

DayDiff = DateDiff("d", Date1, Date2)

CheckNum = DayDiff / 365
'More than one year if result > 1

If CheckNum > 1 Then
NumYear = DayDiff \ 365
'get remainder
DayDiff = DayDiff Mod 365
Else
NumYear = 0
End If

'we will averge out the months to 30 days...
CheckNum = DayDiff / 30
'More than one month if result > 1
If CheckNum > 1 Then
NumMonth = DayDiff \ 30
'get remainder
DayDiff = DayDiff Mod 30
NumDay = DayDiff
Else
NumMonth = 0
NumDay = DayDiff
End If

MsgBox "There are " & NumYear & " year(s), " & NumMonth & " month(s) and " _
& NumDay & " day(s) between " & Date1 & " and " & Date2 _
& ".", vbInformation, "Result"

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
M

macropod

Hello Jean-Guy,

As with the other attempted abbreviated solutions, this doesn't deal with
leap years correctly. You need to get that part right before you can
extrapolate to months and days.

So, for an approximation starting with:
DayDiff = DateDiff("d", Date1, Date2)
Years could be calculated as:
INT((DayDiff+1)/365.2425)
Months could be calculated as:
INT((DayDiff+1-INT(Years*365.2425))/365.2425*12)
Days could be calculated as:
INT(DayDiff-INT(Years*365.2425+Months*365.2425/12))

Cheers
 
J

Jean-Guy Marcil

macropod was telling us:
macropod nous racontait que :

....and a total disregard for leap years!

Thanks!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
M

Martin Seelhofer

Hello again

While I fully agree with you that those simple solutions suggested
in this thread don't produce *perfect* results, you might want to
have another look at the original question:
I am doing a userform which asks the user to enter date of birth from
a list box and was wondering if anyone knows the code which will
calculate the age of that person on their last birthday.
Click to expand...

Seen that "on their last birthday" thing? Looks like the original poster
was interested in the number of years only...

By the way...
On what date would a person born on 29/2/2004 celebrate their 1st birthday
under your algorithm?
Click to expand...

That would be 2005-03-01. What did *you* expect?


Cheers,
Martin
 
M

macropod

Convention has it that persons born on Feb 29th celebrate their birthday on
the 29th of February, be it the 28th or 29th.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display weeks from a Milestone 0
New computer, how do i move outlook 365 ENTIRELY? 0
Calculating age from date of birth and ignoring if blank 3
calculated form field 1
Search Sheet Names Across Excel Files 0
How do I calculate a persons age from birth dates 1
Recover Outlook calendar entry 1
calculate date of birth with an end date and age 1

Top