calculate age of a person

[Paul15]

Dear all,

In have a working spreadsheet where data is inputted via a user form. On this form the current code uses the date of data entry (todays date) and the date of birth or a person. It then automatically calculates the age of the person and places this on the user form and is ultimately submitted to the spreadsheet. Currently the calculation is 'rounding up' the age. ie

todays date = 16/02/2022, Dob = 25/03/1998, current answer will be 24 years of age. when actually age is 23yrs plus 10 months.

'Calculates age automatically and enters into Me.Age field
  If IsDate(Me.TextBox1.Value) Then
        Me.Age.Value = DateDiff("yyyy", DateValue(TextBox1.Value), Me.date81)
    End If

Date81 = todays date
TextBox1 = date of birth
Age = calculated age of person

I only require a year and not y/m/d of the person

many thanks all in advance

Regards

 

[Benison69]

Not tested yet, but work out number of days, divide by 365, and set the value to the first 2 in the string [LEFT(VALUE,2)]

If IsDate(Me.TextBox1.Value) Then
Me.Age.Value = DateDiff("dddd", DateValue(TextBox1.Value), Me.date81)/365
Me.Age.Value = LEFT(Me.Age.Value,2)
End If

 

[Phuoc]

Me.Age.Value = DateDiff("yyyy", DateSerial(Year(DateValue(TextBox1.Value)), 1, 1), Me.date81)

 

[Paul15]

Not tested yet, but work out number of days, divide by 365, and set the value to the first 2 in the string [LEFT(VALUE,2)]

If IsDate(Me.TextBox1.Value) Then
Me.Age.Value = DateDiff("dddd", DateValue(TextBox1.Value), Me.date81)/365
Me.Age.Value = LEFT(Me.Age.Value,2)
End If

Hi, have incorporated your code into my system and unfortunately I get a compiler error. hopefully the image will explain

 

[Paul15]

Me.Age.Value = DateDiff("yyyy", DateSerial(Year(DateValue(TextBox1.Value)), 1, 1), Me.date81)

hi, have incorporated the code and on my system it still rounding up the calculation. any pointes please

 

[Paul15]

Dear all, thanks to those who have responded. I have come across an article elsewhere as follows:

DateDiff("y", ..., ...) merely subtracts the year parts of the dates, without reference to the month or day. This means we need to subtract one if the person has not has their birthday this year. The following expression returns True if the person has not had their birthday this year

I would guess this is where my error is and where I am looking for my fix.

Looking forward to any answers

KRs

Paul

 

[flyingtony]

I used the following to solve for age:

Rounddown(yearfrac(today(),[cell location of DOB]),0)