CodeItBetter Programming Another VB Programming Blog

'Math - How to Calculate Age
Option Explicit
 
Public Function CalculateAge(ByVal StartDate As Date, ByVal EndDate As Date, _
    ByVal TimeUnit As Integer)
    ' args: StartD & EndD
    ' args: TimeUnit 1=year, 2=month, 3=day, 4=hour, 5=minute, 6=second
    ' returns: integer age in selected unit
    Dim TD As Date
    Dim X As Integer, Y As Integer
    Dim Unit As String 'yr/mon/day/hr/min/sec DateAdd constants
    Dim strMsg As String
    Dim UnitFlag As Boolean 'indicates if return value is + or -
    If TimeUnit < 1 Or TimeUnit > 6 Then
        strMsg = MsgBox("Time Unit Argument must be integer 1 to 6", vbOKOnly, "Argumnet Error")
        CalculateAge = 0
        Exit Function
    End If
    If EndDate >= StartDate Then
        UnitFlag = True
    Else
        UnitFlag = False
        TD = StartDate
        StartDate = EndDate
        EndDate = TD
    End If
    For X = 1 To TimeUnit
        'for 5 time measurement units ("S" for seconds does not calc properly!)
        Select Case X
        Case 1
            Unit = "YYYY"           'year
        Case 2
            Unit = "M"              'month
        Case 3
            Unit = "D"              'day
        Case 4
            Unit = "H"              'hour
        Case 5
            Unit = "N"              'minute
        End Select
        If X < 6 Then
            Y = 0
            ' increment y & StartDate unit by 1 unit, until StartDate > EndDate
            Do While DateAdd(Unit, 1, StartDate) <= EndDate
                StartDate = DateAdd(Unit, 1, StartDate)
                Y = Y + 1
            Loop
        Else
            ' use datediff on seconds and add 1 to result
            Y = DateDiff("S", StartDate, EndDate) + 1
        End If
    Next X
    CalculateAge = Y * IIf(UnitFlag, 1, -1)
End Function
 
'How to use this function:
'Debug.Print CalculateAge("01/01/1969", Now, 1) & " Years"