14 Replies Latest reply on Mar 25, 2015 9:23 PM by biggorilla

# Calculating Age

I'm not very experienced with calculations and need a little help with a calculation that the result is a persons age when you have monthbirth / daybirth / yearbirth in seperate fields, this gives you a value in a dob_ct field. (showing total dob as 04/10/1954 instead of seperate fields). The calc then shows age as a number 58 and needs to also break down the age to month day year when the person is less than 2 years old.

I know some of you wizz's out there can handle this.......

Greg

• ###### 1. Re: Calculating Age

Hi Greg -

Here are two calculations that might help here... the second one for "AgeText" shows a field called "DOB" which you could swap for your "dob_ct" field.  Variables could also be used if necessary to neaten up the second calculation - e.g. use a "Let" calculation to first set a \$dob variable and \$currentdate variable.  I'm sure there could be a better way to do the second one also.  AgeDecimal is handy as it allows for easy sorting and searching of age ranges etc.

AgeDecimal (calculation type is number) = Round ( (Get (CurrentDate ) - dob_ct ) /365.25 ; 2 )

AgeText =

GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( DOB ) - If ( Get ( CurrentDate ) <  Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Yrs, " &

GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( DOB ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( DOB ) ; 1 ; 0 ) ; 12 ) ) & " Mths, " &

GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( DOB )  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( DOB ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( DOB ) ; Day ( DOB ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days"

• ###### 2. Re: Calculating Age

Greg -

A simplified calculation will give you the age in years:

Let ( [ todaysDate = Get ( CurrentDate ) ; DOB = Date ( monthField ; dayField ; yearField ) ] ;

Year ( todaysDate ) -

Year ( DOB ) -

(

todaysDate < Date (

Month ( DOB ) ;

Day ( DOB ) ;

Year ( todaysDate )

)

)

)

I'm not sure what you mean by "break down the age to month day year". Can you elaborate? Do you want a number of years, a number of months, and a number of days? Or do you want to call out that the child is "X months" old, as parents often do?

Mike

• ###### 3. Re: Calculating Age

Hey Mike, thanks again…….. as far as under 2, looking for year/months/days…… this is for a medical application and they asked for that break down,

Gregory V. Klimetz

gvklimetz@mac.com

• ###### 4. Re: Calculating Age

Still not sure what that means. If the child is, say, exactly 18 months old, is that 1 year, 6 months, 0 days? Or are you looking for 1 year, 18 months, and 547.5 days?

• ###### 5. Re: Calculating Age

Sorry, yes I'm looking for 1 year, 6 months, 0 days

Gregory V. Klimetz

gvklimetz@mac.com

• ###### 6. Re: Calculating Age

I had a similar need and modified a calculation first put together by Winfried Huslik so it would behave similarly to the Excel function DateDif.  You can find it here:

http://www.briandunning.com/cf/1326

It does require FileMaker Pro Advanced as it is setup as a custom function. Whenever you need the difference between two dates displayed broken down by years, months and days, just set the flag to "T" and the result will be returned in that format.

HTH,

Brett

• ###### 7. Re: Calculating Age

Thanks, Brett. I was working this up in the shower this morning. Spared me the trouble.

Mike

• ###### 8. Re: Calculating Age

Keep in mind calculating "months" requires some arbitrary decision making on your part.

You will inevitabilty end up with ages of the same number of days but different month and day results. Basically, any solution will be off by a couple days at times.

• ###### 9. Re: Calculating Age

Agreed. There are several threads on this discussion you can check out. In the end, Greg, you'll have to decide if this covers your needs as no solution to this problem is perfect.

Brett

• ###### 10. Re: Calculating Age

Thanks everyone that has sent me some help………

Gregory V. Klimetz

gvklimetz@mac.com

• ###### 11. Re: Calculating Age

Hi Mike - thanks for this calculation! I just plugged into my solution and it worked great!  I understand the Let statement but I'm not sure how the 2nd part of the calculation works?

Year ( todaysDate ) -

Year ( DOB ) -

// why do we need " < " in the calculation - does it only evaluate when today's date is less than the Date (DOB)?

(

todaysDate < Date (

Month ( DOB ) ;

Day ( DOB ) ;

Year ( todaysDate )

)

)

)

Also - I need to calculate how old someone will be on 9/1 of the current year.  Would I substitute 9/1/Year Get (CurrentDate) for "todaysDate"?

Thanks Again!

• ###### 12. Re: Calculating Age

The calculation does this (in English):

Subtract the DOB year from the current year. Then subtract 1 if you haven't yet reached your birthday this year.

The "todaysDate < DOB" part is a Boolean expression that will evaluate to 1 if it's true (i.e., if the current date is earlier than the birthdate). That way, you will get an accurate reading.

For example, this is 2015. I was born in 1966. If you subtract those, you get 49. But since I haven't reached my birthday yet this year, I'm still 48.

To answer your other question, yes, you can substitute another date for todaysDate. Just use:

Date ( month ; day ; year )

In your example, that would be:

Date ( 9 ; 1 ; Year ( Get ( CurrentDate )))

HTH

MIke

• ###### 13. Re: Calculating Age

Making your script or function multi-phasic is useful

Let's name the caluclation/custom function GetAge , you can name it as you please.

Then a simple parameter can be passed:

If age < 2

getage("Months")

else if (xxx)

getage("Years and Months")

else

getage("Years")

end if

Disregarding leap years and birthdates of Feb 29 which really make things difficult for such calculations, let's let FileMaker do the work not to mention those that reverse day, month, year for their own or system settings...

Convert your three fields into a FileMaker Date Field

dateofbirth = date(datefieldmonth;datefieldday; datefieldyear )

birthdatethisyear = date(datefieldmonth;datefieldday; year (get(currentdate) )

Now you have two considerations for age: is the birthdate greater or less than today.

birthdate =

if ( birthdatethisyear < get(currentdate)

Years = year(dateofbirth) - year(get(curentdate)

else

Years = year(dateofbirth) - year(get(curentdate) + 1

end if

(You might check which one of the above gets the +1)

Months are a bit trickier since they loop 1 to 12 but the idea is the same.

Stardates are much easier.  Why it is important to know that a child is two months rather than three months is interesting. Obviously some big change occurs on the first day of each month? Or some statistician might not have a job otherwise?

• ###### 14. Re: Calculating Age

Thank you Mike!  Great explanation!  I didn't know you could do a Boolean expression in a Calc without an If or Case statement - (maybe that's why I'm a newbie).  I was born in 1961 so I'm older but not wiser!

Have a great week!

Alberto