1 2 Previous Next 16 Replies Latest reply on Sep 12, 2012 10:28 AM by philmodjunk

# Age: Calculating from Timestamp - Birth

### Title

Age: Calculating from Timestamp - Birth

### Post

I'm sure there have been many previous requests, but coming from Excel where such a calc is easy, I'm have trouble understanding or doing the complexity of an FM calc.

I'd like to get: Age (accurate and as a decimal) from a Record Timestamp - Birth date.

But I'd like to understand the underlying logic too!

Thanks,

Stephen

• ###### 1. Re: Age: Calculating from Timestamp - Birth

Originally from LaRetta:

use this calculation:

Let ( [
Today = Get (CurrentDate) ;
bDay = Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Today ) )
] ;
Year ( Today ) - Year ( Birthdate ) - ( Today < bDay )
)

This must be an unstored calculation or it will not update correctly as time passes.
--------------------------------------
Or
--------------------------------------
Case ( DOB and Get ( CurrentDate ) ; Year ( Get ( CurrentDate ) ) - Year ( DOB ) - ( DayOfYear (Get ( CurrentDate ) ) < DayOfYear ( DOB ) ) )

---------------------------

• ###### 2. Re: Age: Calculating from Timestamp - Birth

How would this work for where the CurrentDate is actually a TimeStamp from another Layout/Table called 'Record::Date_created'.

I tried substituting but was told the above field was not allowed.

• ###### 3. Re: Age: Calculating from Timestamp - Birth

Use GetAsDate ( TimeStampField )

Though a use of a TimeStampField instead of a date field seems odd here.

• ###### 4. Re: Age: Calculating from Timestamp - Birth

Int((GetCurrentDate)-GetAsDate(theTimeStamp))/365)

• ###### 5. Re: Age: Calculating from Timestamp - Birth

The first suggestion now works (last didn't) but with Record::Date_created = 2012 - BirthDate = 2009 it gave an answer of -2010 whereas it should have been 3 years.

• ###### 6. Re: Age: Calculating from Timestamp - Birth

This expression makes no sense: Record::Date_created = 2012 - BirthDate = 2009

Can you copy and paste the exact expression from the specify calculation dialog?

• ###### 7. Re: Age: Calculating from Timestamp - Birth

Let ( [
Today = GetAsDate (Record::Date_created) ;
bDay = Date ( Month ( BirthDate ) ; Day ( BirthDate ) ; Year ( Today ) )
] ;
Year ( Today ) - Year ( BirthDate ) - ( Today < bDay )
)

• ###### 8. Re: Age: Calculating from Timestamp - Birth

Except that I use a field of type date instead of type timestamp, I use that expression frequently in an unstored calculation field and it works for me. (Must be unstored in order for it to update automatically as time passes.)

Note that Date_created must contain either a date or a timestamp. 2012 is not either of those. And BirthDate must store a date in a field of type date. 2009 is not a date, it's a number.

• ###### 9. Re: Age: Calculating from Timestamp - Birth

Birthdate was 09/09/2009

Record timestamp was 09 Sept 2012 21:51

• ###### 10. Re: Age: Calculating from Timestamp - Birth

Using this form of the expression in the data viewer, I get the correct result of 3 years.

Let ( [DC = GetAsTimeStamp ( "09/9/2012 21:51" ) ;
Birthdate = GetAsdate ( "09/09/2009" ) ;
Today = GetAsDate ( DC ) ;
bDay = Date ( Month ( BirthDate ) ; Day ( BirthDate ) ; Year ( Today ) )
] ;
Year ( Today ) - Year ( BirthDate ) - ( Today < bDay )
)

The changes that I made were simply so that I could test this in the data viewer In FileMaker Advanced.

Note that you cannot enter 09 Sept 2012 21:51 into a time stamp field and not get an error. I can only assume that this is how the data is displayed after it is entered as the data can be shown in this format with the appropriate date format selected once the data has been correctly entered into the field..

• ###### 11. Re: Age: Calculating from Timestamp - Birth

Can I upload to you the file; it would save time?

• ###### 12. Re: Age: Calculating from Timestamp - Birth

Typo in my calculation and I use int to get birthday without decimal point.

The Calculation with decimal

(GetAsDate(theTimeStamp)-Birthdate)/365

The Calculation without decimal

Int((GetAsDate(theTimeStamp)-Birthdate)/365)

• ###### 13. Re: Age: Calculating from Timestamp - Birth

Relevant fields are:

Patient:: BirthDate & Age

Record:: Date_created