Need help calculating Age in FileMaker Pro

Discussion in 'Mac Programming' started by mcarnes, Apr 26, 2007.

  1. mcarnes macrumors 68000

    mcarnes

    Joined:
    Mar 14, 2004
    Location:
    USA! USA!
    #1
    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!
     
  2. Sayer macrumors 6502a

    Sayer

    Joined:
    Jan 4, 2002
    Location:
    Austin, TX
    #2
    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:

    Code:
    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.
     
  3. Mydriasis macrumors 6502

    Mydriasis

    Joined:
    Mar 17, 2005
    #3
    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)
     
  4. mcarnes thread starter macrumors 68000

    mcarnes

    Joined:
    Mar 14, 2004
    Location:
    USA! USA!
    #4
    It worked! That was easy, thanks! You guys are so smart.
     
  5. CanadaRAM macrumors G5

    CanadaRAM

    Joined:
    Oct 11, 2004
    Location:
    On the Left Coast - Victoria BC Canada
    #5
    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.
     
  6. jsalzer macrumors 6502a

    jsalzer

    Joined:
    Jan 18, 2004
    #6
    365.25

    "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.
     
  7. MyFirstMac macrumors newbie

    Joined:
    May 11, 2007
    #7
    What about this type of birthdate?

    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.
     
  8. jsalzer macrumors 6502a

    jsalzer

    Joined:
    Jan 18, 2004
    #8
    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!
     
  9. MyFirstMac macrumors newbie

    Joined:
    May 11, 2007
    #9
    OK. This didn't work for me -- got a huge negative number, like a 37 year old was -53212

    This got it! Thank you so much for saving my sanity!
     
  10. terrypenney macrumors newbie

    Joined:
    Jul 29, 2008
    #10
    This works for me, accurate to the day.

    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.
     
  11. erinanne macrumors newbie

    Joined:
    Jun 11, 2009
    #11
    This works!

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

    Joined:
    Apr 10, 2010
    #12
    Thank you from myself as well Terrypenney. I too have used this code and it seems to work very well for my application.
     
  13. rabone macrumors newbie

    Joined:
    Apr 10, 2010
    #13
    This works for me, accurate to the day.

    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.
     
  14. terrypenney macrumors newbie

    Joined:
    Jul 29, 2008
    #14
    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.
     
  15. marxmyth macrumors newbie

    Joined:
    Sep 30, 2010
    #15
    Conditional calculation of the age

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

    This is simple and precise.
     
  16. Bhbloom macrumors newbie

    Joined:
    Oct 1, 2010
    #16
    You need to set STORAGE to off, ie recalculate each time, to make it automatically update.
    Thanks for the other tips.
     
  17. JRuben macrumors newbie

    Joined:
    Jul 14, 2011
    #17
    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.
     
  18. JRuben macrumors newbie

    Joined:
    Jul 14, 2011
    #18
    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 )​
     

Share This Page