Age from Date of Birth calculation doesn't work.

[MikeKD]

Hi folks,

Is there anything wrong with the calculation below (swiped from Filemaker's own website if I remember correctly) correct?

It does give ages, but they're wrong & I can't work out what it's doing.

 

 

GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( DateOfBirth ) - If ( Get ( CurrentDate ) <  Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( DateOfBirth ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( DateOfBirth ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( DateOfBirth )  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( DateOfBirth ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( DateOfBirth ) ; Day ( DateOfBirth ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days"

 

Cheers!

Mike

[comment]

Is there anything wrong with the calculation below

 

Where would I start? Let me ask you instead: why do you need such calculation (years, months and days) when it's meaningless and does not have a single "correct" answer anyway? See, for example:

http://fmforums.com/forum/topic/54237-years-in-business-calc/?p=256168

[MikeKD]

Thanks for that!

The rough information is useful for comparing achievement for kids - it doesn't have to be exact at all, a few days makes no difference, whereas a few months could.

Would I be better working out years & days? Or Years to decimal places? Is there a "best practice" solution for this?

 

Cheers,

MIke

[comment]

Is there a "best practice" solution for this?

 

Not that I know of. It really depends on your purpose. For example, in pediatrics you normally calculate the age of infants in (whole) months where a month is calculated as something between 30 and 31 days.

 

If you still want a "pretty" years, months and days display, with no pretense at representing anything that could be meaningfully measured, try something like =

Let ( [
today = Get (CurrentDate) ;
elapsedMonths = 12 * ( Year ( today ) - Year ( DateOfBirth ) ) + Month ( today ) - Month ( DateOfBirth )
- ( Day ( today ) < Day ( DateOfBirth ) ) ;

temp = Date ( Month ( DateOfBirth ) + elapsedMonths ; Day ( DateOfBirth ) ; Year ( DateOfBirth ) ) ;
limit = Date ( Month ( DateOfBirth ) + elapsedMonths + 1 ; 0 ; Year ( DateOfBirth ) ) ;
bDay = Min ( temp ; limit ) ;

y = Div ( elapsedMonths ; 12 ) ;
m = Mod ( elapsedMonths ; 12 ) ;
d = today - bDay
] ;
y & " Years, " & m & " Months, " & d & " Days"
)

[MikeKD]

Many thanks for this.

It seems to be months out, e.g.

 

DoB 27 Sep 1999 = 14 years, 0 months 16 days.

21 Jun 2000 = 13 yr, 3 months, 22 days.

 

The odd day here & there isn't an issue, but months are significant! Any idea what's happening? - does FM take today's date automatically from the system, or do I need to set it up somewhere?

[comment]

DoB 27 Sep 1999 = 14 years, 0 months 16 days.

21 Jun 2000 = 13 yr, 3 months, 22 days.

 

I am not sure how you got those results. I am getting "14 Years, 9 Months, 20 Days" for the former and  "14 Years, 0 Months, 26 Days" for the latter (with current date being July 17, 2014).

 

 

does FM take today's date automatically from the system

 

Yes, it does. Make sure you are using an unstored calculation field, with the result type set to Text.

[comment]

Note that your results can be reproduced if today is assumed to be October 13, 2013. Perhaps your system date is wrong? That could also explain why you had problems with the FMI calc - which although very poorly written is still essentially "correct" (at least upon superficial examination).

[MikeKD]

Changing the field type sorted it.

I'm not sure which change it was, but mistake candidates were:

• "don't replace existing contents"
• & type as number
• I don't think I had it as a calculation field either!!

Other than that, a masterful display of competence on my part!!

 

Many thanks for your help :-)

Mike

Ah, I reckon Oct 2013 must be around about when I added the field - it must have just stayed as that because I didn't allow FM to update the field contents.

 

Thanks!

Mike

[Josh Ormond]

What you had was a text field, with the Auto-enter options set. That field will not update unless you do something to make it update.

 

From the Manage Database dialog, you field type needs to be "Calculation". Result ( set in the calculation dialog ) needs to be text. Storage options set to 'unstored'.

 

• "don't replace existing contents"
• & type as number
• I don't think I had it as a calculation field either!!