Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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. .
Join Date
1st September 2010
Posts
10,912

## 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. ## 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. I agreed to these rules
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. .
Join Date
1st September 2010
Posts
10,912

## 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. I agreed to these rules
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

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

#### 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