Average age using birthdays, is it possible?

[Grafixx01]

So I have a table that has a persons birthday in it, listed as MM/DD/YYYY. I'm being told that the people who want to use this DB want to be able to find the average persons age. I figure it's a query but I'm not sure if or how to use mm/dd/yyyy to then get their current age as of *today* and then calculate the average of the age of people in the table.



Anyone? Ideas?

[Welshgasman]

I would just calculate to months and then average on that.
Use Datediff() to calculate the months, then the average. Then divide by 12.?

That is unless they want it really accurate, to the days?.

[Micron]

Calculated field TheAge: (Date-[DOB])/365 or maybe (Date()-[DOB])/365 where DOB is the name of your birth date field. If you don't like the results in terms of decimals, then you'll need to specify what you want to see. It gets more complicated if you're not happy with the likes of 46.73

[Grafixx01]

I would just calculate to months and then average on that.
Use Datediff() to calculate the months, then the average. Then divide by 12.?

That is unless they want it really accurate, to the days?.

No, they want it to the day.

Calculated field TheAge: (Date-[DOB])/365 or maybe (Date()-[DOB])/365 where DOB is the name of your birth date field. If you don't like the results in terms of decimals, then you'll need to specify what you want to see. It gets more complicated if you're not happy with the likes of 46.73

I don't think there would be a problem if it was in decimal as it still gives them a full date of age average.

[Grafixx01]

Calculated field TheAge: (Date-[DOB])/365 or maybe (Date()-[DOB])/365 where DOB is the name of your birth date field. If you don't like the results in terms of decimals, then you'll need to specify what you want to see. It gets more complicated if you're not happy with the likes of 46.73

Would that be done as a query? I was thinking about possibly trying to embed that into a command button to just "calculate on click" or run it with a plot line on a graph and then maybe try to put straight line / dot on the average?

[Peter M]

Use DateDiff with the "d" syntax for the number of days.
DateDiff("d",[DOB],Date())

[Grafixx01]

Can I do that as a command button? Or query? That's what I'm also stuck on. I think it may be better to just have a command button that shows "Average Age", click it and then it displays the age in a dialog box. But I wonder if I can do the other method where it takes the birthdays in like a line graph and then shows the average age in a horizontal line across the entire graph?

[Micron]

As I noted, my suggestion was to create a calculated field and that was in a query because that's what you asked for. If I type
?(date - #03/21/1994#) / 365 in the immediate window I get 27.0301369863014 (not my age!) so you can see what I meant by returning decimals. In a query that would look something like

SELECT (date() - #03/21/1994#) / 365 As TheAge;

Instead of a date you'd use the DOB field if it's a date data type. You could also round if you're OK with Access deciding whether or not to round up or down as it sees fit.

SELECT Round((Date()-#3/21/1994#)/365,2) AS TheAge;

That results in 27.03.
As others have noted, if you want something like "24 years, 6 months and 3 days" or "24 years and 183 days" you're going to have to concatenate DateDiff results together.

[Grafixx01]

Thanks, sorry if I repeated things. I haven't messed with Access since like 2008 / 2009 and people at my work just expect me to know it like I did. So I apologize for being a novice.

[Micron]

No apologies necessary. At least not from my perspective. Let us know how you make out.

[keviny04]

Dividing dates by 365 and then averaging them isn't right because not all years have 365 days, and the OP asked for a precise result. You actually need to do the reverse: average the birth dates themselves, then format the result to your desire. Date values are stored in Access as the number of days since the year 1900, so it's perfectly fine to average the birth dates directly. After getting the "average birth date," you use DateDiff to get the TOTAL NUMBER OF DAYS between it and today's date, and that will be your AVERAGE AGE EXPRESSED IN TOTAL DAYS: AverageAgeInDays = DateDiff("D", AverageDOB, Date)

To express total days as Y years and D days is the tricky part, because a year could be 365 or 366 days, so you can't just divide no. of days by 365. DateDiff also won't help you here, because it will just do a straight subtraction of the year value, which won't work. For example, DateDiff("yyyy", #12/31/20#, #1/1/21#) will return 1 year, even though the true difference between the two dates is ZERO year and one day. So we have to write a special function for this. It will need to subtract 1 from the year when necessarily.

If the average age is Y years and D days, then D is actually the number of days AFTER THE LAST BIRTHDAY of the "average birth date." So we need a special function to get the last birthday too. That puts us in the same dilemma of whether to subtract 1 from the year or not. E.g. if the average birth date is Sep-01-1990, then its last birthday was in last year, i.e. Sep-01-2020. But if average birth date is Feb-01-1990, its last birthday was in THIS YEAR, i.e. Feb-01-2021.

And if the average birth date is in the future, we should return the message "can't calculate average age." So the final VBA code is:

Function GetLastBirthday(ByVal DOB As Date) As Date
    If DateSerial(Year(Date), Month(DOB), Day(DOB)) > Date Then YearOfLastBirthday = Year(Date) - 1 Else YearOfLastBirthday = Year(Date)
    GetLastBirthday = DateSerial(YearOfLastBirthday, Month(DOB), Day(DOB))
End Function

Function GetYears(ByVal BeforeDate As Date, ByVal AfterDate As Date) As Long
    y = Year(AfterDate) - Year(BeforeDate) - 1
    If y < 0 Then y = 0
    GetYears = y
End Function

Sub AverageAgeInYearsDays()
    AverageDOB = DAvg("[DOB]", "Table1")
    If AverageDOB > Date Then
        Debug.Print "Average birth date is in the future; can't calculate average age!"
        Exit Sub
    End If
    AverageAgeInDays = DateDiff("D", AverageDOB, Date)
    Debug.Print "Average Age is " & GetYears(AverageDOB, Date) & " years and " & Date - GetLastBirthday(AverageDOB) & " days!"
    Debug.Print "In case you wonder, the average birth date is " & Format(AverageDOB, "mmm-dd-yyyy") & "."
End Sub

If your birth dates are:

DOB
2/1/1970
3/20/1985
3/28/2001
10/9/1990
8/8/2008
2/5/1988

Run the AverageAgeInYearsDays sub and you will get:

Average Age is 30 years and 206 days!
In case you wonder, the average birth date is Sep-01-1990.

If you have all infants in the group:

DOB
2/1/2021
3/8/2021
1/15/2021
12/18/2020
2/5/2021
2/9/2021

You will get:

Average Age is 0 years and 56 days!
In case you wonder, the average birth date is Jan-29-2021.

[keviny04]

I just made several changes to my previous post, in case anyone is following this thread. Expressing N days as Y years and D days was more challenging than it first appeared. To get the answer, you must know the exact date when the N-day period starts (or ends), because you don't know exactly how many leap years are in that period unless you know at least the start date or the end date. Thanks to the OP for a most interesting question.

[isladogs]

@keviny04
Many thanks for an interesting and detailed solution to the OP's problem.
I've done a few tests using your code focusing on the often tricky issue of leap day birthdays

First of all for anyone else who tries the code you will need to dim a few variables:

Dim YearOfLastBirthday As Integer, y As Integer, AverageDOB As Double, AverageAgeInDays As Integer

Anyway, for someone born on say 29 Feb 1952, I noticed your code for GetLastBirthday gives 1 Mar 2021

?GetLastBirthday(#2/29/1952#)
01/03/2021

Whilst I agree with that interpretation. you may be interested in a lengthy discussion at Bytes.com where an MVP insisted it would be 28 Feb 2021.
In fact, both interpretations are used e.g. to calculate when someone is eligible for state pension in different countries

Anyway, continuing with that example as my average DOB, I tried changing the system date and made a few tests on your AverageAgeInYearsDays procedure.

Today's Date is Mar-01-2020
Average Age is 67 years and 1 days!
In case you wonder, the average birth date is Feb-29-1952.


Today's Date is Mar-01-2021
Average Age is 68 years and 0 days!
In case you wonder, the average birth date is Feb-29-1952.


Today's Date is Feb-28-2021
Average Age is 68 years and 365 days!
In case you wonder, the average birth date is Feb-29-1952.


Today's Date is Mar-27-2021
Average Age is 68 years and 26 days!
In case you wonder, the average birth date is Feb-29-1952.

As far as I can tell, your code passed all the these tests (and more) perfectly. Very impressive!

In case its useful to anyone, also from the above mentioned thread, this is my very simple expression for calculating age in Years:

DateDiff("yyyy", [DOB], Date) + (Format([DOB], "mmdd") > Format(Date, "mmdd"))

Or as a function:

Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer    
   AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
End Function

[isladogs]

Here's another piece of code that others may find useful. It was written by Graham Seach back in 2001 to calculate differences between any 2 dates using any units you want

Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _Optional ShowZero As Boolean = False) As Variant
'Author:    ? Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
 
On Error GoTo Err_Diff2Dates
 
   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booCalcWeeks As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim lngDiffWeeks As Long
   Dim varTemp As Variant
 
   Const INTERVALS As String = "dmyhnsw"
 
'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
         Exit Function
      End If
   Next intCounter
 
'Check that valid dates have been entered
   If IsNull(Date1) Then Exit Function
   If IsNull(Date2) Then Exit Function
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function
 
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If
 
   Diff2Dates = Null
   varTemp = Null
 
'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)
   booCalcWeeks = (InStr(1, Interval, "w") > 0)
 
'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If
 
   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If
 
   If booCalcWeeks Then
      lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("ww", lngDiffWeeks, Date1)
   End If
 
   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If
 
   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If
 
   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If
 
   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If
 
   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
   End If
 
   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
      End If
   End If
 
   If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
      If booCalcWeeks Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
      End If
   End If
 
   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
      End If
   End If
 
   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
      End If
   End If
 
   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
      End If
   End If
 
   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
      End If
   End If
 
   If booSwapped Then
      varTemp = "-" & varTemp
   End If
 
   Diff2Dates = Trim$(varTemp)
 
End_Diff2Dates:
   Exit Function
 
Err_Diff2Dates:
   Resume End_Diff2Dates
 
End Function

Examples of usage:

?Diff2Dates("y",#5/15/52#,#12/29/2020#)68 years
 
?Diff2Dates("y",#5/15/1952#,Date())
68 years
 
?Diff2Dates("y",Date(), #5/15/1952#)
-68 years
 
?Diff2Dates("ymd",#5/15/52#,#12/29/2020#)
68 years 7 months 14 days
 
?Diff2Dates("ymd",#12/29/2020#, #5/15/52#)
-68 years 7 months 14 days
 
?Diff2Dates("ymdhns",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 7 months 14 days 7 hours 20 minutes 11 seconds
 
?Diff2Dates("ys",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 19725611 seconds
 
?Diff2Dates("ymds",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 7 months 14 days 26411 seconds
 
?Diff2Dates("ymd",#2/29/1892#, #3/1/1897#)
5 years 1 day

[keviny04]

@isladogs

Thanks for your comments. You're right that I should consider those who were born on Feb-29 in leap years having their birthday on Feb-28 in non-leap years instead of Mar-01, even though Mar-01 is socially acceptable. Access, however, has conflicting calculations about this. E.g. DateAdd("m", 12, #Feb 29, 2020#) would return Feb 28, 2021. But DateAdd("y", 1, #Feb 29, 2020#) curiously would return Mar 01, 2021. My calculation doesn't depend on how Access calculates it so I could definitely change it to my desire, such as adding an optional variable in my function.