Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA Age calculator

  1. #1
    Join Date
    23rd November 2012
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,916

    Re: VBA Age calculator

    Many ways to do it, including using DATEDIFF... One way (Ignoring DateDiff):
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th February 2013
    Location
    India
    Posts
    133

    Re: VBA Age calculator

    try this sample

    VB:
    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") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd November 2012
    Posts
    13

    Re: VBA Age calculator

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st September 2010
    Posts
    7,916

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd November 2012
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Tax Calculator
    By suchanmv in forum EXCEL HELP
    Replies: 2
    Last Post: December 28th, 2012, 10:51
  2. Bmi Calculator
    By Ozzy88 in forum EXCEL HELP
    Replies: 9
    Last Post: February 25th, 2007, 00:17
  3. Vba Calculator
    By ka0sx in forum EXCEL HELP
    Replies: 3
    Last Post: January 29th, 2007, 08:41
  4. Calculator
    By chrisxs5 in forum EXCEL HELP
    Replies: 4
    Last Post: September 27th, 2005, 05:25
  5. Calculator
    By digikiwam in forum EXCEL HELP
    Replies: 2
    Last Post: May 21st, 2003, 22:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno