Calculation for age (years, months, days)

[neb]

I have used FileMaker Pro 6 in the past and just upgraded to FileMaker Pro 7. I need to do a calculation of a student's age. The age needs to appear in year, month,day format. In the past, I would export the data in FM to Excel, where I can do the calculation with the following formula:=DATEDIF(C2,D2,"y") & ";" & DATEDIF(C2,D2,"ym") & "." & DATEDIF(C2,D2,"md"). However, I have not been successful in calculating age in Filemaker. Can anyone help provide the formula for me? Thanks, in advance, for your help.

[transpower]

Let ( DateNow = Get ( CurrentDate ) ;

GetAsText ((Year (DateNow) - Year (birthday) - If (DateNow < Date (Month (birthday); Day (birthday); Year (DateNow)); 1; 0)) & " Years, " &

GetAsText (Mod (Month (DateNow) - Month(birthday) + 12 - If (Day (DateNow) < Day (birthday); 1; 0); 12)) & " Months, " &

GetAsText (Day (DateNow) - Day (birthday) + If (Day (DateNow) >= Day (birthday); 0; If (Day (DateNow - Day (DateNow)) < Day (birthday); Day (birthday); Day (DateNow - Day (DateNow)))))) & " Days" )

[sbg2]

Let ( DateNow = Get ( CurrentDate ) ;

GetAsText ((Year (DateNow) - Year (birthday) - If (DateNow < Date (Month (birthday); Day (birthday); Year (DateNow)); 1; 0)) & " Years, " &

GetAsText (Mod (Month (DateNow) - Month(birthday) + 12 - If (Day (DateNow) < Day (birthday); 1; 0); 12)) & " Months, " &

GetAsText (Day (DateNow) - Day (birthday) + If (Day (DateNow) >= Day (birthday); 0; If (Day (DateNow - Day (DateNow)) < Day (birthday); Day (birthday); Day (DateNow - Day (DateNow)))))) & " Days" )

A different method is attached to my post. I went with the idea that you can get within 1 year of a persons age just by subtracting a persons birth year from the current year. Then the only problem was to have a calculation return -1 if a persons birthday had not occured yet this calendar year (or 0 if it had), the formula I used is as follows:

Floor(Int(DayOfYear(Get(CurrentDate)) - DayOfYear(Date(Day(Birthdate);Month(Birthdate);Year(Get(CurrentDate)))))/1000)

Full age calculation is:

(Year(Get(CurrentDate)) - Year(Birthdate)) + Floor(Int(DayOfYear(Get(CurrentDate)) - DayOfYear(Date(Day(Birthdate);Month(Birthdate);Year(Get(CurrentDate)))))/1000)

*** Hmmm... Maybe I didn't read the full question! You also need months and days? As in Student X is 19 Years, 4 months and 12 days old?

age.zip

[comment]

I am somewhat puzzled:

1. DayOfYear(date) returns a number equal to the number of days from the beginning of the year of date.

So when your calc is performed in a non-leap year, all those born between February 29 to December 31 of a leap year will have their birthday shifted one day forward, and vice-versa.

2. Why do you find it necessary to enclose this

DayOfYear ( date2 ) - DayOfYear ( date1 )

with the Int function()? Do you expect a possible non-integer result?

3. What does Floor ( x / 1000 ) do?

[sbg2]

Sorry finished in a hurry yesterday, attached is the revised database which explains better. I removed the Int function, my mind told me I need to return an Integer between 0 and -1 then I forgot to remove the Int statement once I realized I needed to use floor instead of Int.

It also has a much simpler formula for age which I think might work:

Floor((Get(CurrentDate) - Birthdate)/365.25)

Not absolutely sure this will return the correct birthdates for leap years, but I'm not about to change my system clock to test it. Any thoughts?

age2.zip

[comment]

The question is - what is the purpose?

If you want a "quick an' dirty" result, then your last formula is in the right direction. Except that the average Gregorian calendar year is 365.2425 days, not 365.25. So that

Int ( Get ( CurrentDate ) - Birthdate ) / 365.2425 )

would be appropriate.

If, OTOH, you want to be alerted when someone's birthday is coming up, then the quick an' dirty method won't be good enough. You don't want to be a day or two off wishing someone a happy birthday.

So it's good that you have corrected your original formula. However, I believe that if you remove all the unnecessary calculations that you are forcing the CPU to cycle through, you will get back to:

Year(Date2) - Year(Date1) - ( Date2 < Date ( Month(Date1) ; Day(Date1) ; Year(Date2) ) )

(where Date2 would be the current date, and Date1 the birthday).

For example, your

(DayOfYear(Date2) - DayOfYear(Date(Month(Date1);Day(Date1);Year(Date2))))

is the same as

Date2 - Date ( Month(Date1) ; Day(Date1) ; Year(Date2) )

And there's no need to perform the Floor ( x / 1000 ) exercise just "to force all negative numbers to = -1 and all positives (and 0) = 0".

You simply make a comparison a > b. That will return 1 if true, 0 if false. Subtract that and you're done.

BTW, you don't have to change your system clock to test a calc using Get(CurrentDate). Simply change all references of Get(CurrentDate) to TestField. Then you can plug any date into TestField to simulate the calc being done on that day.

[sbg2]

365.2425? I did actually look it up before I made the formula. You are right of course, it is usually better to use a formula that will obtain an accurate as possible result. Who knows, maybe 300 years from now the average age of a student may be greater than 128 years old? I however think for this particular case being accurate within 128 years is satisfactory. If you need greater accuracy than 128 years using the Gregorian Calendar equivalent would be more appropriate.

Thanks for pointing out that I could drop the DayOfYear part from the formula, I completely missed that one when developing the formula. There is a need to perform the Floor(/1000) exercise, but only because it was a part of my personal agenda which was to determine if I could develop a formula that didnt use an "if" statement to calculate age. Why would I do such a thing? Well, I may need to use a similar formula in a program that doesnt support "If" within a formula, but mostly because I was curious if I personally could come up with that formula.

[comment]

I do not understand the significance of 128 years. If by that you mean that between 1904 to 2100 the average year is 365.25 days, then yes - if you limit yourself to that range. Still, this is on average, so you cannot guarantee accuracy in a specific case.

Better put, I can guarantee you will be inaccurate in roughly 25% of the cases.

I may need to use a similar formula in a program that doesnt support "If"

What program supports the Floor() function, but does not support a simple a > b comparison?

[Ollie Dog!]

Thank you all for your posts on this topic. I was just about to ask the same question myself when I came upon this wonderful thread. You all have saved me countless hours. It would seem calculating one's age would be a fairly common task to many applications. Couldn't FileMaker just include this as simple function (i.e., a drag and drop solution we could simply plug into a calculation field)?

Anyway, thanks again.