Basic Age (years old) question 4

[IMFCSDMN]

How can I find someone's age using their Date of Birth and another date. I tried =datediff("y",Date1,Date2) and it's not accurate.

 

[Darrylles]

Hi,

Why's it not accurate?

It'll give you the difference in years between date1 and date2.

How accurate do you want?

Regards,

Darrylle





"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[tonyflavell]

Hi

Use the following:

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a given date to today's date.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, 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 given date.
' If the given date is a birthday, the function returns the number of
' months since the last birthday.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birthday).
'
' RETURN
' Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal StartDate As Variant) As Integer
Dim tAge As Double

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

tAge = (DateDiff(&quot;m&quot;, StartDate, Now))

If (DatePart(&quot;d&quot;, StartDate) > DatePart(&quot;d&quot;, Now)) Then
tAge = tAge - 1
End If

If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

 

[IMFCSDMN]

Thanks.

I knew I could of prepared the code provided by tonyflavell but I was hoping there was a simplier way.

 

[tonyflavell]

Hi

If anyone does know an easier way, I want to know as well!

Tony

 

[mpastore]

Int(DateDiff(&quot;m&quot;, [Date1], [Date2]) / 12) + IIf(Month([Date1]) = Month([Date2]) And Day([Date1]) > Day([Date2]), -1, 0)

 

[TonyJollans]

Hi IMFCSDMN,

Datediff limits you to specifying a single unit of measurement but dates are just numbers to Access and you can perform arithmetic on them and format any (valid) number as a date or a period of time so the following will tell a tale.

MyAgeToday = Format(Date - DateValue("07/09/1952"), "yy ""Years"", mm ""Months"" & dd ""Days""")

Enjoy,
Tony

 

[tonyflavell]

Hi Tony

Your method calculates my age as 57 Years, 06 Months & 12 Days
when in actual fact it is 57 Years, 05 Months & 13 Days

The 29 Days difference is 2 x the number of leap years since I was born.

Tony

 

[mpastore]

Here's modification to code I poseted earlier, had not accounted for leap years

Int(DateDiff(&quot;m&quot;, [date1], [date2]) / 12) + IIf(Month([date1]) = Month([date2]) And (Day([date1]) > Day([date2]) And Not (Day([date1]) = 29 And Day([date2]) = 28)), -1, 0)

 

[TonyJollans]

Hi Tony,

You are right, my formula doesn't work. What I thought should be a period is in fact a date and, in your example, represents the 12th of June (that is 5 months and some into the year). The odd day difference is due to the different number of leap years in your life compared to the number in the first fifty-odd years from the date calculation base.

I'm still learning, best ignore my post, Sorry!

Tony

 

[TonyJollans]

Hi All,

I hate dates! I've been back and read the whole thread and ...

mpastore - I think your formula was better before the modification. With date1 = 29/02/1952 and date2 = 28/02/2004 the original is correct and the modified one wrong. With date1 = 29/02/1952 and date2 = 28/02/2003 I don't know which is right because I don't know what counts as a birthday when the 'real' birthday doesn't exist.

IMFCSDMN - for the record, the formula in your original post gives days of life not years of life

Tony - All in all I think your code (which can be compressed into a single statement if wanted) is as good as it gets.

Enjoy,
Tony