Is their an accurate way of calculating this in Filemaker Pro. Currently I have a DOB field and the calculation ((Today - DOB)/365)-. This gets it within a few months but I want it to be accurate to the day.

Thank you.

Is their an accurate way of calculating this in Filemaker Pro. Currently I have a DOB field and the calculation ((Today - DOB)/365)-. This gets it within a few months but I want it to be accurate to the day.

Thank you.

The following will give you age in days:

Let (

[

dob = Date ( m ; d ; y )

; today = Get ( CurrentDate )

; age = today - dob

] ;

age

)

The resulting number can then be parsed into whatever format you want your result to be in. Keep in mind, though, that age expressed as years, months and days will not always be the same for a given span of dates (e.g. 3 months can be 89, 90, 91 or 92 days depending on which set of 3 months is used, and in which year), nor will total number of days always be the same (e.g. 5 years can be 1,826 or 1,827 depending on whether the span contains one leap year or two).

OK, let me explain:

1. In the Date() function, m, d and y stand for month, day and year. Substitute the letters for numbers—e.g. Date ( 11 ; 14 ; 2015) is today's date. Alternatively, substitute the entire expression for a date field (e.g. Date of Birth if you have that in your database).

2. The calc I have posted as an example uses the Let() function, which is one you really should get to know. The syntax of this function is "Let ( {[} var1 = expression1 {; var2 = expression2…]} ; calculation )". To explain this, the first part from the first { to the second } is where you declare one or more variables The squiggly brackets indicate stuff that is optional—there must be at least one variable declared, others are optional. If there are more than one variable, each must be separated by a semicolon, and the entire set must be contained within square brackets. The second part, calculation, is the expression you want as the result of the entire function.

3. In my calc, dob, today and age are all variables. Because I don't want to use these variables outside of this calculation I simply name them; if I wanted to use them outside the calc (in a script, say) I would preface each with a $ or $$. Note that once a variable is declared inside the Let() calc, it CAN be referenced by other variables that follow it, hence my use of dob and today to calculate age.

4. Although age is actually the result I want, I have declared it as a variable and then referenced it as the result calculation, although I could have simply used the calc, today - dob, as the second part. This is a technique I prefer because it is easier to test this result and other variables as I build the calc.

5. I have used line breaks and tabs to format the whole calc in a way that makes it more readable. It could have been written as simply: Let ( [ dob = Date ( m ; d ; y ) ; today = Get ( CurrentDate ) ] ; today - dob ) but I find that to be less clear.

g4guitar schrieb:

Thank you Otmar,

Sorry I don't know what '_agedate' is referring to. Is that supposed to be field or something else?

David

keywords has explained the Let-function, so that's the same here:

_agedate = GetAsDate ( Get ( CurrentDate ) + 1 - DOB )

fills the local variable _agedate with the difference of today + 1 and DOB in days, converts the number of days back to a Date-format. As GetAsDate ( 1 ) (one day) returns 01/01/0001 but should be 00/00/0001, I add +1 to the current date and then subtract 1 on the Year, Month, Day-functions.

If you just need the year, just return Year ( _agedate ) - 1.

Otmar

- 1 person found this helpful
twickleberry:

Assuming you already have a Date field named "Date Of Birth", create a calculation field "Age" (Number result, Do not store calculation results) with the formula:

Let ( today = Get (CurrentDate) ;

Year ( today ) - Year ( Date Of Birth ) -

If ( today < Date ( Month ( Date Of Birth ) ; Day ( Date Of Birth ) ; Year ( today ) ) ; 1 ; 0 ) )

TSGal

FileMaker, Inc.

Do you need something like this...?

Let (

[

dob = ( 11 ; 4 ; 2015 ) ; // parameter or a DOB field…

today = Get (CurrentDate) ;

%years = year ( today ) - year ( dob ) ;

%months = month ( today ) - month ( dob ) ;

%days = day ( today ) - day ( dob )

] ;

%years &" years, " & %months & " months and " & %days & " days!"

)

—> Result: 0 years, 0 months and 9 days!

/Stefano