calculating age in calculation field

[dlazenby]

Field 1: Date of birth

Field 2: Current date or entered date

Field 3: Current age (calculation field)

Question: Specifically how do I set up the calculation field for field 3? I have tried to figure this out by searching the online help and the post histories for calculating age. Sorry if this has been covered elsewhere.

Thanks.

Doug Lazenby

[ESpringer]

Do you need anything beyond the number of full years completed? Assuming you've got BirthDate as one Date-type field:

If

(DayofYear(BirthDate)=<DayofYear(Get(CurrentDate));

Year(CurrentDate)-Year(BirthDate);

(Year(CurrentDate)-Year(BirthDate))-1)

I haven't used it, but check and see whether the logic makes sense to you.

[Michael Myett]

There seem to be plenty of age calculations floating around, but you must be careful its reliable for all situations. This one seems to be bullet-proof.

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

Where DOB equals Date of Birth, result is an unstored number.

[agutleben]

This was published by DataIsland Software. It looks like what you are looking for.

// This is a Custom Function version of calculations provided in "Advanced FileMaker Pro 5.5" by Chris Moyer and Bob Bowers. This formula differs slightly from the ones in the book which do not account for leap years.

Let ( leapFactor = If ( Mod ( Year ( theDate ) ; 4 ) = 0 ; 1 ; 0 );

Case (

// Age in years

format = 1 ; Year ( theDate ) - Year ( birthDate ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( birthDate ) );

// Age in years and days

format = 2 ; Year ( theDate ) - Year ( birthDate ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( birthDate ) ) & " years and " & Case (

( DayOfYear ( theDate ) - leapFactor ) ? DayOfYear ( birthDate ) ; DayOfYear ( theDate ) - leapFactor - DayOfYear ( birthDate ) ;

DayOfYear ( theDate ) + ( DayOfYear ( Date ( 12 ; 31 ; Year ( theDate ) ) - DayOfYear ( birthDate ) - leapFactor ) ) ) & " days" ;

// Age in years, months and days

Format = 3 ; Year ( theDate ) - Year ( birthDate ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( birthDate ) ) & " years and " & Mod ( Month ( theDate ) - Month ( birthDate ) + 12 - (Day ( theDate ) < Day ( birthDate ) ) ; 12 ) & " months, and " & (theDate - Date ( Month ( theDate ) - (Day ( theDate ) < Day ( birthDate ) ) ; Day ( birthDate ) ; Year ( theDate ) ) ) & " days"

)

)

[dlazenby]

I appreciate everyone's thoughtful replies. I think I made it too complicated. I just need the age in years. I fooled around with the calculation field and this is what I did which seems to get the job done:

In the calculation dialog box I typed:

(CurrentDate-patientmaindata::PatientDOB)/365.

I then formatted the number as a decimal with no numbers behind the decimal point to give me a whole number.

It is an unstored value--I don't know how to make it stored, or whether that is more desirable-- I do wish I could store it.

This should also work for leap years I think.

Doug

[dbruggmann]

Why do you want to store it? The calculation becomes static and won't show the "current" age any longer, if it is stored (unless one of the involved values changes for a record).

[Ron F]

try this. If the DOB field is left blank for some reason you won't get the "?" in the age field. This also takes into account the leapyear factor by using 365.25 vs. 365. Good luck!

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

[DanBrill]

Messing around with the quarter days isn't reliable. Here's what I use

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

In English this says: 'Subtract the year of birth from this year, and if they haven't had their birthday yet this year, subtract 1 more from that.'

The internal date stuff takes care of all the leap years.

Dan

[Michael Myett]

Why bother with the If statement, a boolean expression will always return a 0 or a 1.

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

[DanBrill]

Good point. I guess I sacrificed a few electrons for the sake of clarity so I could remember what I was doing when I looked back on it later. But yours acrually does the job more efficiently.