How to calculate an accurate age of a Person 2

[JamesBBB]

Hi all,

I have a problem in trying to accurately calculate the age of people. The information I have is their date of Birth and obviously the day the calculation is run (i.e. today’s date)

In the reports upbound field I make the:-
control source = CInt((Date()-[date of birth])/365)

The problem is some ages are correct however some are showing 1 year older than they should be, for instance:-

Mr Smith 10/5/1964 43 (Should be 42)
Mr Jones 22/3/1957 50 (should be 49)

For info the date the report was run is 1/3/07

I haven’t got a clue how to sort this one, can anyone please help

Many thanks

James

 

[PHV]

= DateDiff("yyyy", [date of birth], Date()) + (Format([date of birth], "mmdd") > Format(Date(), "mmdd"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HarleyQuinn]

The reason for this is the Cint() function you are using rounds to the nearest integer. If you use the Int() function which just strips the decimal portion of the number you should see the correct (in most cases) results.

I would also divide the result of the subtraction by 365.25 ofr a more accurate calculation.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

Hi,

n = Int((Date - [DOB]) / 365)

Skip,

_[red][/red]

 

[combo]

Mind the leap years. I'd use directly:
n=(year(Date)-[DOB])-iif(month(Date)<=month[DOB],iif(day(Date)<day[DOB],1,0),0)

combo

 

[AnotherHiggins]

I only see one correct answer so far.

Let's consider a birthday of May 8, 1972.

PHV, That seems to work just fine.

HarleyQuinn, your method doesn't report that the age changes until the day after the birthday. That may or may not be acceptable.

Skip, your formula will fail for dates between 4/30 - 5/7 (falsely returning that the age is already 35).

Combo, your formula will fail for dates between 4/8 - 4/30 (falsely returning that the age is already 35).

Unfortunately, I didn't look at PHV's until I spent some time on this on my own and had already came up with the considerably more combersome:
=YEAR(DATE) - YEAR(DATEOFBIRTH) - IF(MONTH(DATE) < MONTH(DATEOFBIRTH), 1, IF(MONTH(DATE) = MONTH(DATEOFBIRTH), IF(DAY(DATE) < DAY(DATEOFBIRTH), 1, 0), 0))

James: I'd go with PHV's answer.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JamesBBB]

Chaps

Many many thanks for all of this information it is greatly appreciated and I'll use PHV's code as suggested.

Again Many many thanks

James