Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Age calculator

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Age calculator

    Sup guys. I have a small predicament. I need to calculate the exact age of a person in excel vba i.e years & months. I have strdOB for date of Birth and strDate for todays date. How do i go about this one. Thanx in advance.

  • #2
    Re: VBA Age calculator

    Many ways to do it, including using DATEDIFF... One way (Ignoring DateDiff):
    Code:
    Public Function ExactAge(BirthDate As Variant) As String
    
        Dim iYear As Integer
        Dim iMonth As Integer
        Dim d As Integer
        Dim dt As Date
        Dim sResult  As String
        
        If Not IsDate(BirthDate) Then Exit Function
        
        dt = CDate(BirthDate)
        If dt > Now Then Exit Function
        
        iYear = Year(dt)
        iMonth = Month(dt)
        d = Day(dt)
        iYear = Year(Date) - iYear
        iMonth = Month(Date) - iMonth
        d = Day(Date) - d
    
        If Sgn(d) = -1 Then
            d = 30 - Abs(d)
            iMonth = iMonth - 1
        End If
    
        If Sgn(iMonth) = -1 Then
            iMonth = 12 - Abs(iMonth)
            iYear = iYear - 1
        End If
        
        sResult = iYear & "y " & iMonth & "m " ' & d   & "d"
    
        ExactAge = sResult
    
    End Function

    Comment


    • #3
      Re: VBA Age calculator

      try this sample

      Code:
      Dim strdob, strdate As String
      strdob = "06/06/1978"
      strdate = Date
      MsgBox ((DateDiff("m", DateValue(strdob), strdate) \ 12) & " Years and " & (DateDiff("m", DateValue(strdob), strdate) Mod 12) & " Months")

      Comment


      • #4
        Re: VBA Age calculator

        Great guys, that works. but how do i get that age in number form?????????

        Comment


        • #5
          Re: VBA Age calculator

          It would have helped if you stated that first... You cannot have years and months as a number - so please explain exactly, and in full, what you want.

          Comment


          • #6
            Re: VBA Age calculator

            Sorry for the mix up. The latter was supposed to be a part of the question. I would also need to calculate the exact age as a number. Thank you very much for your assistance. Grateful in advance.

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X