# Thread: Formula for Age (Office XP )

1. ## Formula for Age (Office XP )

Hi

I have a date field =now() and date field, date of birth, I created a number field and I want it to subtract dob from datenow and show as a two digit number.
ie = now()-dob result 63 or whatever the result, at the moment I get an unregonisable 4 digit number even though I have set the format to 2 digits.

Many Thanks

2. ## Re: Formula for Age (Office XP )

Dates are numbers measured in days. So if you subtract one date from another, you get the number of days between them.
Divide the answer by 365 to get the number of years.

3. ## Re: Formula for Age (Office XP )

John's suggestion gives a good approximation, but doesn't take leap years into account. Dividing by 365.25 is slightly more accurate.

Here is a function that was published originally by Microsoft:

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
Age = Year(DateToday) - Year(Bdate)
If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Age - 1
End If
End Function

4. ## Re: Formula for Age (Office XP )

Hi John

That did it for me, but gave me another problem I now want to query that field, I entered it as and expr1 and criteria Like[enter age:] but it comes up with 0

Many Thanks

5. ## Re: Formula for Age (Office XP )

Hi Hans

Thanks for that but as you will know by now I am not to briliant with function I don't quite wher to insert it

Regards

6. ## Re: Formula for Age (Office XP )

Type or paste the function into a standard module. You can then use it in a query as follows:

Age: Age([DateOfBirth],Date())

If you would prefer not to use code, you must ensure that you get an integer result:

Age: Int(([DateOfBirth]-Date())/365.25)

In both cases, replace DateOfBirth with the name of the birth date field in your table

7. ## Re: Formula for Age (Office XP )

Hi Hans

Thanks for the reply for ease of use for myself I prefer the second method not the code, I works fine but I am struggling to get the age to come out in query I have tried it with an Expr but I don't seem to be able to get it to work

8. ## Re: Formula for Age (Office XP )

What have you tried that doesn't work?

9. ## Re: Formula for Age (Office XP )

I found that this worked.

10. ## Re: Formula for Age (Office XP )

Hi John

I followed your instruction but as you see below all I get is blank fields.

11. ## Re: Formula for Age (Office XP )

Hi Hans

See my screenshot reply to John.

Thanks

12. ## Re: Formula for Age (Office XP )

Take a closer look at my <post#=313180>post 313180</post#> and John's <post#=313253>post 313253</post#>. You omitted a pair of brackets. The expression should be

Age: Int<font color=red>(</font color=red>([datenow]-[dob])/365.25<font color=red>)</font color=red>

13. ## Re: Formula for Age (Office XP )

Hi Hans

Am I dumb or what, Sorry for wasting your time

<img src=/S/bash.gif border=0 alt=bash width=35 height=39>

14. ## Re: Formula for Age (Office XP )

If exact age is not critical then dividing days by 365.25 is reasonably accurate but could be a day or so out. If you need absolute accuracy you need to go further. I use the following function that takes a birthdate as the argument. It finds the number of years between the dates and then checks for whether today is before or after the day the birthday falls in THIS year, and takes away a year if necessary.

Function AgeYear(varBD As Variant)
Dim varAge As Variant
On Error GoTo HandleErr

On Error Resume Next
varAge = DateDiff("yyyy", varBD, Now())
If Now() < DateSerial(Year(Now()), Month(varBD), Day(varBD)) Then
varAge = varAge - 1
End If
AgeYear = varAge

ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "mdlGeneral.AgeYear" 'ErrorHandler:\$\$N=mdlGeneral.AgeYear
End Select

End Function

Regards

15. ## Re: Formula for Age (Office XP )

Hi David

I'm afraid I don't understand how functions work and as it not that important to be exact I will stick with the simpler formula.

I am grateful for your response.

Thank you