wondering if anyone has any tips on how to calculate someone's age from their date of birth to the current date. there doesnt seem to be any function that gives current date except for the time date stamp. would i use that in a calculation to create an equation that gave current age? i've played with the calculations and have not been able to get it to accept that as an option to so far. any advice would be welcome

FileMaker Version: 7

Platform: Mac OS X Jaguar

The function to get the current date is Status(CurrentDate)

For FM6 and earlier: This will return a text string with the age like: 23 years, 5 months, 14 days

If( c.DOB > Status(CurrentDate),

"",

Year(Status(CurrentDate)) - Year(c.DOB) - If(Status(CurrentDate) < Date(Month(c.DOB),Day(c.DOB),Year(Status(CurrentDate))), 1, 0) &

" years, " &

(Mod(Month(Status(CurrentDate)) - Month(c.DOB) + 12 - If(Day(Status(CurrentDate)) < Day(c.DOB), 1, 0), 12)) &

" months, " &

(Status(CurrentDate)- Date(Month(Status(CurrentDate)) - (Day(c.DOB) > Day(Status(CurrentDate))), Day(c.DOB), Year(Status(CurrentDate))))

& " days"

)

For FM7 (I have not personally tested this):

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

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

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"

Oh yea...

Nancy->

Welcome to the forums!

My first line wasn't complete... It should say

The function to get the current date is Status(CurrentDate) in FM6 and earlier and Get(CurrentDate) in FM7.

Sam

thank you for your help. that is an amazingly long string of equations, i dont feel so bad for not figuring it out on my own now, but sheesh, you;d think that would be a really common funtion that people would want to get all the time, and not have to write um..five lines of commands to get it...thanks again and thanks for your welcome to the forum ,too :>

FileMaker Version: 7

Platform: Mac OS X Jaguar

one last question on this point, the DOB would refer to the column or field titled DOB, correct, but how do i set a field for CURRENT, as in CURRENT DATE, that is a command function i cannot find, is it up to me to set up three data fields one for current year, one for current month and one for current day and refec back to them to find each of the numbers then, and if so, i apologize if I'm asking an obvious question, but how do i set up those fields to show wach of those variables, day, month and year current?

Nancy->

In my formula, c.DOB was a calculated date of birth (based on validated input), I should have edited my post to use DOB defined as a standard date field.

In FM6 and early, the current date is Status(CurrentDate), in FM7 it's Get(CurrentDate).

So, to set a field for current month, set the field to auto-enter this calculated value:

Month(Status(CurrentDate)) in FM7: Month(Get(CurrentDate))

For the current day: Day(Status(CurrentDate)) in FM7: Day(Get(CurrentDate))

Do read about this functions in the manual or help so you're sure you know what your getting.

The age calculation is one of those things you don't take the time to reinvent, I copied mine from someone else & tweaked it.

Sam

what manual? i have all the manuals i know about, couldnt find anything in the ones that came with the program version 7 that explained it, also have filemaker 5 and 6 manuals, bibles and visual basic advanced version for 6 and 'about fliemaker' for six, none of them addressed this clearly, maybe i missed that page somehow, but i did look, several times. thank you again for your help. this is the first relational database i've put together and its confusing, at least in part because all the in depth info manuals i have are for prior versions, nothing out on 7 except web stuff so far, if you have any directions to point me in for better resource info i'd love to know where to look, i've ended up here in the process of doing exactly that search. thanks again for all your help!

Nancy->

In FM6 and earlier, the functions are listed in the manual. FM7's manual does not list the functions, you have to use the built-in help.

Function descriptions are not the best, but you can see what's available. IN this case, I was just pointing out the functions for manipulating dates: Month(), Day() Year(), etc.

Grasping the concepts of relational databases can be difficult. I find that most explanations are too detailed. I explain it using a file cabinet metaphor. For example, at a school, one drawer may have forms with a person's address, SS#, etc. Another drawer may have grades & classes for each term. Humans know how to read the name and pull forms from both drawers. Relational databases are all about having the computer know how to pull the forms from the drawers (relating the name on the forms).

These forums are a great resource. I search them for solutions often. My fist attempt is to try to narrow what I'm looking for to 1 forum and search there. If that doesn't work, I search all forums.

And don't be shy about asking for help. It distracts me from doing real work, which isn't as much fun

Here's my formula which I use to get age from Date of Birth which seems to work OK with me.

If(D.O.B.;(Today-D.O.B.)/365;0)

colinmac

colinmac,

FYI - in FM7 the Today function no longer exists. You have to use Get(CurrentDate).

Mike

colinmac->

Welcome to the forums.

The Today function can be flakey, most developers recommend replacing it with Status(CurrentDate) to avoid problems. It was kept in FileMaker after an update (I forget, 2 to 3?) for backward compatibility.

Your displays age as years in decimal. In my experience, people want to see age displayed as years, months, and days. In my case, the age was for young children & schools, where they think in years/months for age. Of course, for internal calculations, etc., decimal age is preferred.

My function did not take plural of the words into account, so it would say "1 years, 1 months, 1 days" instead of "! year, 1 month, 1 day". I use it with abbreviations like y m d, so plurality wasn't an issue. If it is, adding an if test works but will increase the complexity of the function & cause it to be slower (which MAY be an issue).

Sam

CyborgSam,

Your not-yet-tested age calculation above works just great--thanks! But another question if I may? How do I go about getting that age calculation to self-refresh as time passes? I would have assumed that it could, since it's built on Get(CurrentDate). But based on a few tests done by changing the system date on my computer, it seems that the age field only updates if/when I do something to the underlying DOB field. Suggestions?

FileMaker Version: 7

Platform: Mac OS X Panther

A calculation field using a Get function must be unstored or else it won't update automatically.

Could you elaborate?

FileMaker Version: 7

Platform: Mac OS X Panther

Go to Define Database --> Fields, select your field and click Options, click Storage Options, check the box next to 'Do not store calculation results'. If it's stored, it won't update until a field that it relies on (in your case DOB) is modified. If it's unstored, it will update automatically.

Thanks. Your directions made sense, but I wasn't finding that option. But then discovered that in my zeal to add these age calculations to my solution, I'd inadvertently set them up as Text fields, with a calculated result. Which did the calculation just fine, but don't have a "do not store" option. Converted to Calculation fields, and all is well.

FileMaker Version: 7

Platform: Mac OS X Panther

Ah, yes, that would be an auto-enter calculation, one which is set at the time of record creation and no longer updates, verses a calculation field which updates whenever a field it references is changed (unless it is unstored).

This simple script I wrote seemed to work OK

If(Month(Today) & Day(Today) < Month(DOB) & Day(DOB), Year(Today)-Year(DOB)-1, Year(Today)-Year(DOB))

Jonathan

FileMaker Version: 5

Platform: Mac OS 9

Jonathan->

Welcome to the forums!

Your calculation will work, but to be 100% bulletproof you should use NumToText() on the Month() and Day() parts of the If test.

Also, Today is an unreliable function left over from previous versions of FileMaker. All uses of Today should be replaced with Status(GetCurrentDate).

Here's my version of your calc:

Year(Status(CurrentDate)) - Year(DOB) -

If( NumToText( Month(Status(CurrentDate))) & NumToText(Day(Status(CurrentDate))) <

NumToText( Month(DOB)) & NumToText(Day(DOB)),

1,

0

)

As I opined previously in this thread, many people do not want to see ages as decimal numbers, they prefer years, months, and for young children, days. Decimal ages are needed when doing calculations, so this is a useful formula.

Sam

We store the creation date of the record in a field called Todays Date so I use that in my calculation as we want the age at quotation for an insurance quote. The date of birth is entered into a field called Date of Birth. I use the DayofYear function like this.

If(DayofYear(Todays Date)<DayofYear(Date of Birth), Year(Todays Date) - Year(Date of Birth)-1, Year(Todays Date) - Year(Date of Birth))

This is far from bomb proof and we do have occasional mis-calculations which I can't understand. Rather than change the calc entirely can anyone see a simple change that I can make to this calculation to make it more accurate? I guess that the problem could be from folks born after February in leap years.

Many thanks.

Sid

The solution referred to is running on Server 5.5 OS 9.2.

'Tis very true that age calcs are quite a pain, much more so than would seem to the typical non-technical person.

This is proababy not that helpful as we are in FMP and not SPSS, but I wish FMP had a "YRMODA" function, like SPSS. In SPSS (statistical package for social sciences), dates are typically stored as number of days since the Julian calendar was started. Then, you can pass YEAR, MONTH, and DAY variables and YRMODA function calcs the number of days since julian calendar was started. Then, you can format this one number. Actually, this might be pretty easy calc or plug-in to add, humm....

ie. compute age=trunc(($jdate-yrmoda(byear,bmonth,bday))/365.25).

Where $jdate is the currennt date. So, you are subtracting one number from the current date (also one number). Pretty simple, but still, not as simple as it would seem!

That code in my last post is SPSS code. YRMODA is very handy in SPSS. The actual date is just one number, and depending on the format that is applied, it is shown in just about any date format. Sorry to be off on a tangent...

Case(IsEmpty(DOB),"",Truncate((Creation Date-DOB)/365.25,0))

You have to use 365.25 vs. 365 otherwise you would not take into account for leap year and the age will be off by a day for every four years of a person's age.

The yrmoda function that gives the julian for a date can be constructed like this:

GetAsNumber( Date( bmonth; bday; byear ) )

Very cool. OK, I don't know if this is the julian calendar or not (SPSS now uses a begin date in the 1500's, and FMP is using some other begin date) but no matter, this is the exact same type of capability.

So just run this as a calc on both your start and end dates. Then, you can subtract the birthdate number from the current date number and you have age in days. Maybe some of the ideas presented earlier are more direct, but I am very comfortable with this. :-)

Thanks a lot,

Hi there, im trying to do the age calculation.

I tried this method:

Year(Status(CurrentDate)) - Year(DOB) -

If( NumToText( Month(Status(CurrentDate))) & NumToText(Day(Status(CurrentDate))) <

NumToText( Month(DOB)) & NumToText(Day(DOB)),

1,

0

)

But it doesnt work in FM 8.5, what should I change? Also, I dont actually need the months and days, only the years. (But days and months should be brought into calculation.

Help would be appreciated, thanks.

Result is number and calc must be unstored:

Year ( Get ( CurrentDate ) ) - Year ( DOB ) - ( Get ( CurrentDate ) < Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( Get ( CurrentDate ) ) ) )