# calculating age in calculation field

This topic is 6428 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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"

)

)

##### Share on other sites

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

##### Share on other sites

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).

##### Share on other sites

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))

##### Share on other sites

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

##### Share on other sites

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 ))))

##### Share on other sites

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.

##### Share on other sites

This topic is 6428 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

• ### Similar Content

• #### Recovery and reopening of FM7 files

By Tony Morosco,

• 2,572 views
• #### Filemaker 6 for Mac issue.

By Tumma K,

• (and 4 more)

Tagged with:

• 8 replies
• 3,396 views
• #### FM3 upgrade to FM Pro 5.5 - Windows 8 installation possible?

By MrEddByrnes,

• (and 4 more)

Tagged with:

• 4,138 views
• #### Moving data from fp5 to fp13

By bmill,

• (and 4 more)

Tagged with:

• 2 replies
• 2,732 views
• #### Filemaker 5.5 database will not delete records on-line

By randyinla,

• (and 4 more)

Tagged with: