Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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
    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Bradd
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula for Age (Office XP )

    What have you tried that doesn't work?

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Formula for Age (Office XP )

    I found that this worked.
    Attached Images Attached Images
    Regards
    John



  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formula for Age (Office XP )

    Hi John

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

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formula for Age (Office XP )

    Hi Hans

    See my screenshot reply to John.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula for Age (Office XP )

    Braddy,

    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. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formula for Age (Office XP )

    Hi Hans

    Am I dumb or what, Sorry for wasting your time

    Braddy
    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    If you are a fool at forty, you will always be a fool

  14. #14
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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
    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •