Age Calculation Flaw?

  • Thread starter Thread starter Poida3934
  • Start date Start date
P

Poida3934

I am trying to calculate the age of pre-school students at start of school
year, and have
a "flaw" somewhere if the birth month is the same as start of year month.
The first formula
below gives me the years, the second the months, can anyone spot the
error???

=DateDiff("yyyy",[StudDOB],[Year_Start])+Int(Format([Year_Start],"mmdd")<Format([StudDOB],"mmdd"))

=DateDiff("m",[StudDOB],[Year_Start]) mod 12

So, a student whose birth date is 4th January 2003, and school year start is
1st January
2008, I get 4 years and 0 months as his age (should probably show 11 months,
hehehe).
Any clues folks? Thanks in anticipation, your help in here has been
tremendous over the last
few weeks.

Poida
 
Actually, both your examples don't include the day of the month.

You *need* to include the day of the month for this to work.
So, a student whose birth date is 4th January 2003, and school year start
is
Click to expand...
1st January
2008, I get 4 years and 0 months as his age (should probably show 11 months,
hehehe).

Using the code example below, I get
? ageyear(#01/03/2003#,#01/01/2008#)
4

True, the person does not turn 5 untill the 3rd of jan

and, the months is
? agemonths(#01/04/2003#,#01/01/2008#)
11

If the school year starts on the 15th, then the birthday WILL have occurred.

? ageyear(#01/04/2003#,#01/15/2008#)
5

? agemonths(#01/04/2003#,#01/15/2008#)
0


Here is the code:

Public Function AgeYear(dtStart As Date, dtend As Date) As Integer

Dim intYear As Integer

intYear = DateDiff("yyyy", dtStart, dtend)

' if month of this year has not yet past, then
' birthday not yet occured subtrack 1 year

If Month(dtStart) > Month(dtend) Then
intYear = intYear - 1
Else
If Month(dtStart) = Month(dtend) Then
' same month, if day has not yet
' passed, then birthday not yet
If Day(dtStart) > Day(dtend) Then
intYear = intYear - 1
End If
End If
End If

AgeYear = intYear

End Function


Public Function AgeMonths(dtStart As Date, dtend As Date) As Integer

Dim intMonths As Integer

intMonths = DateDiff("m", dtStart, dtend)

' if month of this year has not yet past, then
' birthday not yet occured subtrack 1 year

If Month(dtStart) > Month(dtend) Then
intMonths = intMonths - 1
Else
If Month(dtStart) = Month(dtend) Then
' same month, if day has not yet
' passed, then birthday not yet
If Day(dtStart) > Day(dtend) Then
intMonths = intMonths - 1
End If
End If
End If

AgeMonths = intMonths Mod 12

End Function
 
Back
Top