Age Not Calculating Properly

Hello Team,

So I have a Userform that intends to calculate the user age.

Textbox3 = DOB entered by user

Textbox4 = Date entered by user

Commandbutton3 = click to calculate the age

Results of difference go to TextBox6

I have the following code:

The problem with this code is its only calculating the year difference and not exactly what I want.

For instance, DOB of user is 10/1/2002 and the date on textbox4 is 9/1/2020. One look and you can already see this user is below 18.

When I calculate using the above formula, it gives me a result of 18 when it should be 17.

Thank you for any help.

That works great, royUK !

Thank you!

Hello royUK . Hope all is well.

Sorry I have to re-open this thread.

As I said above, your formula works great. But, one of my users encounter a problem with it. Same problem when I open this thread - the date is not calculated properly.

We were trying to calculate the difference between the date 03/02/2002 and 05/22/2020. On the formula you provided above, the result is 17. When we try calculating using Windows Calculator, the difference was 18.

Do you have any idea why this happened?

Thank you!

I am not sure I am following. Can you expand on that?

Is there not a fix we can do?

Here it is royUK . Please check on UserForm8. Just dragged and dropped from the original worksheet. Thank you.

Hi rory . Thank you for this. Let me try it out.

Quote from rory

Try this version:

Code

1. Private Sub CommandButton1_Click()
2.     With Me
3.         Dim DOB As Date
4.         DOB = CDate(.TextBox3)
5.        
6.         Dim AsAtDate As Date
7.         AsAtDate = CDate(.TextBox4)
8.        
9.         Dim iYrs As Integer
10.         iYrs = DateDiff("yyyy", DOB, AsAtDate)
11.        
12.         If DateSerial(Year(AsAtDate), Month(DOB), Day(DOB)) <= AsAtDate Then
13.        
14.             .TextBox6.Value = iYrs
15.        
16.         Else
17.             .TextBox6 = iYrs - 1
18.        
19.         End If
20.    
21.     End With
22. End Sub

Display More

This one works fine based off the 2 examples I gave thus far.

Quick question, rory . How is this different from the one roy initially gave me?

Also, sorry this could be a noob question, but the code below, what does it mean or its function?

You guys are a life saver. Thank you so much to both of you rory and royUK .

That's the weird thing royUK . It could have been my mistake not checking for other dates sample. But, the dates I provided on the initial example was working fine with your initial code. I mean, I've been using the same code for some time, and for some reason, it was only today I encounter a difference on the years.

Man, I am not sure what happened. LOL