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

Code
1. Private Sub CommandButton3_Click()
2. With Me
3. Me.TextBox6.Value = DateDiff("yyyy", CLng(CDate(.TextBox3)), CLng(CDate(.TextBox4)))
4. End With
5. End Sub

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.

• Try this

Code
1. Private Sub CommandButton1_Click()
2. Dim iYrs As Integer
3. With Me
4. iYrs = DateDiff("yyyy", CLng(CDate(.TextBox3)), CLng(CDate(.TextBox4)))
5. If DateSerial(Year(CLng(CDate(.TextBox3))), Month(CLng(CDate(.TextBox3))), Day(CLng(CDate(.TextBox3)))) > CLng(CDate(.TextBox4)) Then
6. .TextBox6.Value = iYrs
7. Else: .TextBox6 = iYrs - 1
8. End If
9. End With
10. End Sub
• 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?

• Try this version:

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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

• Try this version:

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?

Code
1. .TextBox6 = iYrs - 1
• I think there was a typo in Roy's original and it should have been using textbox4 for the year, not textbox3.

It basically changes just the year of the DOB field so that it is the same as the year in the other date field, then says if that date is the same as or earlier than the ate in the other field, then just return the number of years difference, otherwise subtract 1 (since the birthday for that year hasn't happened yet).

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• 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