You have two issues: calculating the correct age and making sure the field updates with the passage of time.
The following calculation cannot be used in an auto-entered calculation nor a stored calculation. It must be an unstored calculation:
Let ( T = get ( CurrentDate ) ;
Year ( T ) - Year ( DOBfield ) -
( T < date ( Month ( DOBfield ) ; Day ( DOBfield ) ; Year ( T ) ) )
) // let
Date of Birth in your case here is a date, you are subtracting a date from a number, which will not work.
Age is such a common calculation that filemaker actually has a whitepaper on it:
From that page, Age is calculated:
Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - If ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )
Notice that they are using the Year() function around the Birthdate field as well, which you were not. They are also using an If() calculation on if the additional year should be selected for the current year.
So in your case, just substitute your fields into the above:
Year ( Get ( CurrentDate ) ) - Year ( _Date of Birth ) - If ( Get ( CurrentDate ) < Date ( Month ( _Date of Birth ) ; Day ( _Date of Birth ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )
Also, I tend to avoid leading underscores in field names, they can cause trouble when you least expect it. Same with numbers, special characters and long strings of words.
Thanks. I'm not happy with the underscores leading the field names myself, but I imported all the fields from another database that didn't play nice when I was updating machines and I had no choice and I had to use Filemaker to rebuild my entire database. It was too much trouble....along with doing my every day job....to try to rename all the fields. It's been months of trying to find free time to get some of the fields to be filled in correctly and work somewhat correctly. This age one is more annoying than anything, but when a kid needs to be evaluated, I'd like the age to be correct.
I'll see what I can do with your suggestion. It's been a long time since I had to actually think about calculations and how they all worked together. Thanks again.
I update my current date every day, this is a database I use every day and I do notice changes, but not nearly what they should be. Thanks for your response. It's been so long since I had to run any scripts or calculations that I'm really rusty. I borrowed the one I was using, so I'm amazed that it gave me anything remotely correct.
"updating your current date every day"
will have no no effect on how these calculations update.
The reason why the calculation is not correct is,
date - date
is number of days, if you apply it in year function
Year ( date - date )
the function need parameter as date, so the "number of days" is treated as the day after "number" days from "yesterday of 1/1/1" (1 is treated as 1/1/1, 366 is 1/1/2)
each year have different days with leap year calendar system, so this is not make sense.
But the calculation returns always integer number, so if you get 10.11, you did some other things. (not using unstored calculation field but auto-enter calculation ?)
Here is a custom function that might help: Age ( birthday; today; format )
The function will calculate a persons age. The result of this function can be displayed in four different formats:
2: Years and Days
3: Years, Months and Days.
4: Years ¶ Months ¶ Days
I won't accept that, even with changing it to use my field names.
I need to have an age in years and months. We're talking students, here and I need to know how old with the month for when they will be tested and just how close they are to turning a certain age. I was close to what I needed with the calculation I got here from another discussion, but it's just off by a few months.
So far, none of the suggestions have changed what I'm getting for an age for the student I mentioned above. I expected I would have gotten something different, but so far, nothing. It's always 10 years, 11 months.
I expected I would have gotten something different, but so far, nothing.
I have twice pointed out that all of these calculations can have update issues.
How are you implementing this calculation?
- Setting a field's value in a script?
- A stored Calculation field?
- An Unstored Calculation field?
- A number or text field with an auto-enter calculation?
1. will only update each time you run the script
2 and 4 will not update unless a field referenced in the calculation is modified
4 might not update even if a field referenced in the expression is modified if you don't clear the "do not replace..." check box.
3 will update every time you display the field on a layout, but being unstored brings its own limitations to what you can do with the field.
The suggested custom function is close enough to what you want that very little additional work would be needed to get the Year.Month result that you've requested.