Need help calculating Age in FileMaker Pro

[mcarnes]

A stupid question here, but I can't figure it out.

I want to calculate the age of a patient based on their date of birth. I enter the date of birth in one field, and I want another field to calculate the current age of the patient.

How do I do this? I'm using FileMaker Pro 8.5. Thanks!

[Sayer]

Create a new field (File -> Define -> Database) with a Date type - call it Birth.

Create a new field (File -> Define -> Database) with a Calculation type - call it Age.

Add the following calculation:

If ( Month ( Get ( CurrentDate )) < Month (Birth) ; Year ( Get ( CurrentDate ) ) - Year ( Birth ) - 1 ; Year ( Get ( CurrentDate ) ) - Year ( Birth ) )

Basically this calculation checks the current month against the "birth" month, and if it is earlier than that month calculates the age in years and subtracts one (not quite a full year older). Otherwise the current month and birth month line up and returns the current age in years from birth day to birth day.

[Mydriasis]

Your age field has to be a formula field with the following:

If (
MonthNumber ( Get ( System Date ) ) > MonthNumber ( Birthdate ) ;
Yearnumber ( Get ( System Date ) ) - Yearnumber ( Birthdate ) ;
Yearnumber ( Get ( System Date ) )- Yearnumber ( Birthdate ) - 1 )

'Birthdate' is the Field with the persons birthdate, obviously. Remember to set the field calculate always calculate the result and not save it. And the result-type has to be Number.

(btw. the names may not be accurate, I had to translate from german)

[mcarnes]

Create a new field (File -> Define -> Database) with a Date type - call it Birth.

Create a new field (File -> Define -> Database) with a Calculation type - call it Age.

Add the following calculation...

It worked! That was easy, thanks! You guys are so smart.

[CanadaRAM]

EXcept it may not work during the month of the subject's birthday. IICC In that month, it will promote them to one year older on Day 1 of the month, even if their birthday is on the 28th.

The other way is to use the TRUNCATE function to remove remainders.

(this is not real code, I don;t have 8.5 up on this machine - look the real functions up in Help)

Today - Birthdate = number of days old the person is, correct?
With Leap years, a year is 365.25 days long, So:

Truncate ((Today - Birthdate) / 365.25,0) = YearsOld

This makes the margin of error (depending where the leap years fall) one day rather than one month.

[jsalzer]

EXcept it may not work during the month of the subject's birthday. IICC In that month, it will promote them to one year older on Day 1 of the month, even if their birthday is on the 28th.

The other way is to use the TRUNCATE function to remove remainders.

(this is not real code, I don;t have 8.5 up on this machine - look the real functions up in Help)

Today - Birthdate = number of days old the person is, correct?
With Leap years, a year is 365.25 days long, So:

Truncate ((Today - Birthdate) / 365.25,0) = YearsOld


"Today" is now "Get (CurrentDate)". And commas have been replaced with semicolons for parameters. Other than that, I believe you're good. This is the way I'd do it.

[MyFirstMac]

I've tried the other scripts and am not getting anywhere.

We use an eight digit birthdate - a la - 19970807 is August 7, 1997. If I want to have FileMaker 8.5 do it in the new code, how do I set that up? The code name I'm using is "birthdate"...

I've got a field that converts the 19970807 to a 08/07/1997 format, if that helps. That field is called "birth convert" and it's code is:

"Middle( Birthday; 5 ; 2 ) & "/" & Right( Birthday ; 2 ) & "/" & Left( Birthday ; 4 )"

Just trying to get an age going here. Thanks for any help to this newbie Mac convert.

[jsalzer]

I've tried the other scripts and am not getting anywhere.

We use an eight digit birthdate - a la - 19970807 is August 7, 1997. If I want to have FileMaker 8.5 do it in the new code, how do I set that up? The code name I'm using is "birthdate"...

I've got a field that converts the 19970807 to a 08/07/1997 format, if that helps. That field is called "birth convert" and it's code is:

"Middle( Birthday; 5 ; 2 ) & "/" & Right( Birthday ; 2 ) & "/" & Left( Birthday ; 4 )"

Just trying to get an age going here. Thanks for any help to this newbie Mac convert.

Combine your formula with CanadaRAM's.

Truncate (( Get ( CurrentDate ) - BirthConvert ) / 365.25; 0) = YearsOld

Assuming you're storing BirthConvert as a date. Otherwise, go the longer route of:

Truncate (( Get ( CurrentDate ) - GetAsDate (Middle( Birthday; 5 ; 2 ) & "/" & Right( Birthday ; 2 ) & "/" & Left( Birthday ; 4 )) ) / 365.25; 0) = YearsOld

In which case, you can get rid of your BirthConvert field (unless you need it for something else).

Enjoy!

[MyFirstMac]

Truncate (( Get ( CurrentDate ) - BirthConvert ) / 365.25; 0) = YearsOld


OK. This didn't work for me -- got a huge negative number, like a 37 year old was -53212


Truncate (( Get ( CurrentDate ) - GetAsDate (Middle( Birthday; 5 ; 2 ) & "/" & Right( Birthday ; 2 ) & "/" & Left( Birthday ; 4 )) ) / 365.25; 0) = YearsOld


This got it! Thank you so much for saving my sanity!

[terrypenney]

I find this works as the calculation of a calculated field (I call it StAge for student age. It is a number field)

(Year ( Get ( CurrentDate )) - Year (StDOB))-(If ( Month ( Get ( CurrentDate ) ) < Month ( StDOB ) ; 1 ; If ( Month ( Get ( CurrentDate ) ) = Month ( StDOB ) and Day ( Get ( CurrentDate ) ) < Day ( StDOB ); 1 ; 0 ) ))

Where StDOB is a date field storing the date of birth of a student.

[erinanne]

I find this works as the calculation of a calculated field (I call it StAge for student age. It is a number field)

(Year ( Get ( CurrentDate )) - Year (StDOB))-(If ( Month ( Get ( CurrentDate ) ) < Month ( StDOB ) ; 1 ; If ( Month ( Get ( CurrentDate ) ) = Month ( StDOB ) and Day ( Get ( CurrentDate ) ) < Day ( StDOB ); 1 ; 0 ) ))

Where StDOB is a date field storing the date of birth of a student.

Thank you everyone. TerryPenney, I copied/pasted the above, changed StDOB to my own fieldname, and it worked like a charm. Thank you! ea/

[rabone]

I find this works as the calculation of a calculated field (I call it StAge for student age. It is a number field)

(Year ( Get ( CurrentDate )) - Year (StDOB))-(If ( Month ( Get ( CurrentDate ) ) < Month ( StDOB ) ; 1 ; If ( Month ( Get ( CurrentDate ) ) = Month ( StDOB ) and Day ( Get ( CurrentDate ) ) < Day ( StDOB ); 1 ; 0 ) ))

Where StDOB is a date field storing the date of birth of a student.

Thank you from myself as well Terrypenney. I too have used this code and it seems to work very well for my application.

[rabone]

Thank you from myself as well Terrypenney. I too have used this code and it seems to work very well for my application.

Ah, I find I must question this formula a bit more. I set the birthday to 4/11/1965 in my database YESTERDAY on 4/10/2010 so that when I opened it today, I could see if would update to the correct years. It did not. :(

So, what needs to change in this formula so that it is a dynamic calculation and will update to the age to the correct age after passing a birthday?

Thanks.

[terrypenney]

I just tested it and it still works for me. Just before midnight on the 11th April I set a birthdate to 12 April. As my computer clock passed over midnight, the age did not change, but when I closed the file and reopened it the age was updated. If anyone knows how to make it happen without the need to close and open I would like to know.

[marxmyth]

If (
DayOfYear ( Get ( CurrentDate ) ) ≥ DayOfYear ( DOB ) ;
Year ( Get ( CurrentDate ) ) - Year ( DOB ) ;
Year ( Get ( CurrentDate ) )- Year ( DOB ) - 1 )

This is simple and precise.

[Bhbloom]

You need to set STORAGE to off, ie recalculate each time, to make it automatically update.
Thanks for the other tips.

[JRuben]

If (
DayOfYear ( Get ( CurrentDate ) ) ≥ DayOfYear ( DOB ) ;
Year ( Get ( CurrentDate ) ) - Year ( DOB ) ;
Year ( Get ( CurrentDate ) )- Year ( DOB ) - 1 )

This is simple and precise.

It is simple, but it is not completely precise. It does not take leap years into account, so it will be one day off every four years. For most purposes that is ok, but if you need to be completely precise, like I do, a different calculation is necessary.

[JRuben]

It is simple, but it is not completely precise. It does not take leap years into account, so it will be one day off every four years. For most purposes that is ok, but if you need to be completely precise, like I do, a different calculation is necessary.

While terrypenny's solution works, even for leap years, it is fairly complicated and hard to follow.
marxmyth's solution, corrected to account for leap years, yields much cleaner code.
Here is a solution that is simple and precise (with thanks to marxmyth for the head start), dob is the date of birth in question.

If ( Date ( Month ( Get ( CurrentDate ) ); Day ( Get ( CurrentDate ) ); Year( dob ) ) ≥ dob;
Year ( Get ( CurrentDate ) ) - Year ( dob ) ;
Year ( Get ( CurrentDate ) ) - Year ( dob ) - 1 )

If you move the conditional statement to a custom function (called HasBirthdayPassed, or something like that), you can get even cleaner code.

If ( HasBirthdayPassed ( dob );
Year ( Get ( CurrentDate ) ) - Year ( dob ) ;
Year ( Get ( CurrentDate ) ) - Year ( dob ) - 1 )