Formula for Age 4

[KeithGorham]

Can anyone tell me what formula to use to calculate a person's age. I have a field for the date of birth and need a field that will calculate and update their age on their birth day.

Thanks

 

[luceze]

This will give their current age:

=(Date()-[YourBDayFieldName])

Format the field to yy.

HTH,
Eric

 

[lespaul]

Most developers frown on storing a calculated field. Do you have reason to store how old the person is TODAY? Most calculations are done in queries.

Leslie

 

[KeithGorham]

Leslie,

I have a martial arts school and the classes are divided by age. It helps me to know when it is time to consider moving a child to another class when they come of age.

I tried the formula that Eric responded with, but it doesn't seem to work. Please know that I am a novice with Access, so I may not be entering in the right place. I've tried the formula in the Query and in the form.

I have searched the posts and many have different ways to calculate the age, but most is above my head. I was hoping there was a simple formula for this.

If there are any other suggestions on calculating a student's age, I would greatly appreciate it.

Thanks, Leslie

Keith

 

[Golom]

I think that lespaul's point was that, if you permanently store the age and just refer to it, then it will not change until you permanently store another age ... and since time marches on more or less constantly ... you probably want to compute these ages "on-the-fly" when you ask for them rather than storing them.

luceze's answer does work but you need to do everything suggested
[tt]
= Format ( (Date()-[YourBDayFieldName]), "yy" )
[/tt]

 

[BobJacksonNCI]

Wow, luceze and Golom!

I've read numerous threads on this and some involved quite a bit of VBA code... Thought I'd found the briefest method by:

Int(((Date()-[DOB])+.5)/365.2425)

B4 you ask, + .5 is to show someone is a year older ON their birthday instead of the following day.

But this is more straight-forward!

Certainly worthy of the stars!!
Bob

 

[tomcat21]

Try this:

Age: Int(Year(Now())-Year([DOB])+(Month(Now())-Month([DOB]))/12+(Day(Now())-Day([DOB]))/365.25)

 

[MichaelRed]

While perhaps "O.K." for the'suggested' set of age ranges, many / most / all of the calculations of age from DOB using the fractional day err (i.e. return incorrect results) when dealing with very small differences in the dates. There are several (MANY!) alternatives in these fora (Tek-Tips) which return the CORRECT value regardless of the difference in the dates used, and several of them are suprisingly simplistic.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

Michael is almost always accurate!

And folks who need a formula/function/procedure/expression to calculate the age need only to search this site to find plenty. If someone needes to calculate the exact number of years / months / days ( / hours / minutes / seconds / etc ), add his name to your search to find the VBA routine that does just that.

As far as calculating the correct age in years only, I have not had a single error reported when the expression I suggested was employed. That said, luceze's recommendation is so simple I will use it going forward until an error is reported.



HTH,
Bob

 

[MichaelRed]

Public Function basDob2AgeShort(DOB As Date) As Integer

    '? basDob2AgeShort(Date)
    'Or how to use simple functions to generate _
     erronous results

    Dim Idx As Integer
    Dim MyDt As Date

    MyDt = DOB - 15

'    While Idx <= 30
'
'        Debug.Print DOB, MyDt, Format((Date - MyDt), "yy")
'        Idx = Idx + 1
'        MyDt = MyDt + 1
'
'    Wend

    basDob2AgeShort = Format((Date - DOB), "yy")

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[psemianonymous]

Michael is right. I once had to write an Age function for something like 300,000 records, and the 365.425 doesn't work 100% of the time. Since the age of the patient changed the payment amounts, this was very important to get right.

Here's an oldie/goodie FAQ:

How to calculate age (another method). faq181-85

 

[BobJacksonNCI]

My FINAL post on this thread...
(I hope, and probably many of you, too!)

If you want the accurate age in whole years and don't want to resort to code, use this:

DateDiff("yyyy",[dthire],Now())+Int(Format(Now(),"mmdd")<Format([dthire],"mmdd"))

It is courtesy of Duane Hookum.

My contribution is occasionally off on the day before the anniversary, as demonstrated when the original date was 2/18/2002 and the expression is used with the date of 3/17/2004.
Int(((Date()-[dthire])+0.5)/365.2425)

luceze/Golom's expression:
Format(((Date()-[dthire])),"yy")
does not advance on the anniversary, which I think is probably universally desireable.

Modifying it to:
(((Date()-[dthire])+1),"yy")
might work, but I suspect it may suffer the same shortcoming as my contribution.

This has been an interesting exercise for me...
My test sample is limited, and I don't have access to a larger sample including dates comprising a larger range.

If someone wants to share a table of dates with me, I'd be happy to explore the accuracy of the above expressions, along with other ideas - within reason.

Bob