Calculating age from Date of Birth

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kate - 09 Nov 2004 21:29 GMT

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!

Reply to this Message

Jezebel - 09 Nov 2004 22:17 GMT

Age = Now - DOB

> 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!

Reply to this Message

macropod - 10 Nov 2004 11:03 GMT

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

Reply to this Message

Jezebel - 10 Nov 2004 11:18 GMT

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.

> Hi Kate,
>
[quoted text clipped - 43 lines]
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.788 / Virus Database: 533 - Release Date: 1/11/2004

Reply to this Message

macropod - 11 Nov 2004 07:22 GMT

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

> Wow! talk about overkill on the VBA solution ...
>
[quoted text clipped - 50 lines]
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.788 / Virus Database: 533 - Release Date: 1/11/2004

Reply to this Message

Martin Seelhofer - 11 Nov 2004 18:32 GMT

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.

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

Reply to this Message

macropod - 11 Nov 2004 20:31 GMT

And now for the months and days, with a formatted output as per my example?

> Hey there
>
[quoted text clipped - 29 lines]
> Cheers,
> Martin

Reply to this Message

macropod - 12 Nov 2004 22:05 GMT

BTW Martin,

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

Cheers

> Hey there
>
[quoted text clipped - 29 lines]
> Cheers,
> Martin

Reply to this Message

Martin Seelhofer - 14 Nov 2004 22:56 GMT

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.

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?

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

Cheers,
Martin

Reply to this Message

macropod - 15 Nov 2004 07:42 GMT

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

> Hello again
>
[quoted text clipped - 18 lines]
> Cheers,
> Martin

Reply to this Message

Jean-Guy Marcil - 12 Nov 2004 22:55 GMT

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
[quoted text clipped - 8 lines]
> datedif as a worksheet function in Excel takes those extra issues
> into account.

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
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

macropod - 13 Nov 2004 20:57 GMT

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

> macropod was telling us:
> macropod nous racontait que :
[quoted text clipped - 59 lines]
> End Sub
> '_______________________________________

Reply to this Message

Jean-Guy Marcil - 13 Nov 2004 22:59 GMT

macropod was telling us:
macropod nous racontait que :

>> How about this, using DateDiff to get a rough estimate? I say
>> "rough" because the weakness lies in the number of days per moth.

...and a total disregard for leap years!

Thanks!

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

Malcolm Smith - 10 Nov 2004 12:41 GMT

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

Reply to this Message

Helmut Weber - 10 Nov 2004 14:58 GMT

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/

Reply to this Message