
1. Re: How do I calculate nonrounded whole elapsed years?
philmodjunk Sep 2, 2013 8:23 AM (in response to basilisk2)The following calculation computes age in whole years from a birthdate. It should work to do the same for any anniversary date:
Let ( [ B = YourTable::BirthDateField ;
T = Get ( CurrentDate )
] ;
Year ( T )  Year ( B )  ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
)Substitute your birthdate field in place of "YourTable::BirthDateField" and make sure that this is a field of type date. Also make sure to select unstored as the storage option for this calculation field or the age will not automatically update as time passes.

2. Re: How do I calculate nonrounded whole elapsed years?
basilisk2 Sep 2, 2013 10:58 AM (in response to basilisk2)Brilliant! Thanks for that, it worked nicely. I just added "1 + " to the calculation to get the next anniversary number (rather than the last one) and I have the result I wanted.
Is there any way to format the numbers so that 1 comes out as 1st, 2 => 2nd, 3 => 3rd and the rest become xth? As in 1st Anniversary, 3rd Anniversary , 7th Anniversary etc?
Or should I create a new calculated field and use the Case function, adding the suffix via this second field?

3. Re: How do I calculate nonrounded whole elapsed years?
philmodjunk Sep 3, 2013 9:37 AM (in response to basilisk2)I'd use the case function. You could use one calculation field with both the case function and the above calculation expression. Or you can use two separate calcualtion fields. The end result is the same.

4. Re: How do I calculate nonrounded whole elapsed years?
basilisk2 Sep 3, 2013 9:51 AM (in response to basilisk2)One field would certainly be neater; how do I use them together  concatenate the results or some other method? I suspect I am going to be challenged working this one out! I have a feeling a variable would be needed but I can't see how it would work... any pointers?
TIA!

5. Re: How do I calculate nonrounded whole elapsed years?
philmodjunk Sep 3, 2013 11:24 AM (in response to basilisk2)Let ( [ B = YourTable::BirthDateField ;
T = Get ( CurrentDate )
A = Year ( T )  Year ( B )  ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
] ;
A & Case ( A < 4 ; Choose ( A ; "" ; "st" ; "nd" ; "rd" ) '
"th" )
) 
6. Re: How do I calculate nonrounded whole elapsed years?
basilisk2 Sep 3, 2013 2:47 PM (in response to basilisk2)I copied that into my calculation and got the popup alert "Specified Field cannot be found" so could not adjust the formula. Also, why do I need both Case and Choose? There appears to be a disconnected ' single quote mark there as well, and with four results in the A & Case part of the formula, five if you count the null, is the less than 4 operator correct? Remember I am adding a 1 to the number of years elapsed so as to give the value for the next anniversary, not the last one.
I am slightly confused...?

7. Re: How do I calculate nonrounded whole elapsed years?
philmodjunk Sep 3, 2013 2:52 PM (in response to basilisk2)Sorry for the Typo!
Let ( [ B = YourTable::BirthDateField ;
T = Get ( CurrentDate )
A = Year ( T )  Year ( B )  ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
] ;
A & Case ( A < 4 ; Choose ( A ; "" ; "st" ; "nd" ; "rd" ) ;
"th" )
)You don't have to use Choose, it just simplifies the expression slightly. You could use this instead:
Let ( [ B = YourTable::BirthDateField ;
T = Get ( CurrentDate )
A = Year ( T )  Year ( B )  ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
] ;
A & Case ( A = 1 ; "st" ;
A = 2 ; "nd" ;
A = 3 "rd" ;
"th" ) // last unpaird term like this is the "else" result
) 
8. Re: How do I calculate nonrounded whole elapsed years?
basilisk2 Sep 3, 2013 4:25 PM (in response to basilisk2)Thanks for that clarification. It now works as intended, and with optimised code too! :) I tidied it up a bit as I was getting some funny answers and so my finished code looks like this:
Let ( [
B = SQ Account Setup ;T = Get ( CurrentDate ) ;A = 1 + Year ( T )  Year ( B )  ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )] ;A & Case (A = 1 ; "st" ;A = 2 ; "nd" ;A = 3 ; "th" ;"th" ))I added a (missing?) semicolon at the end of the T = Get ( CurrentDate ) line as it was not the last element. I also separated out the lines of the functions just for the sake of clarity, as would be done with HTML or normal programming code. Not sure why there are so many unnecessary curly brackets in the function templates. I also don't get the pattern in the Choose command? 
9. Re: How do I calculate nonrounded whole elapsed years?
philmodjunk Sep 3, 2013 4:34 PM (in response to basilisk2)Good catch on the missing semicolon, but the only other functional change that I see returns "3th" instead of "3rd". Don't get why you would want that here.
And I don't see any curly brackets {} in your posted example. If you are getting them added in automatically by FileMaker, this means that you have field names that start with a number or include operator symbols or names such as and, or, +,.  and so forth. The brackets then need to be added to keep the code interpreter from mis interpreting the expression. I recommend renaming your fields to eliminate the need for brackets if this is the case.
Choose is similar to setting up an array with a zero index but with the values "chosen" listed as parameters instead of stored in the array.
Choose ( 0 ; "a" ; "b"; "c" ) returns "a". Use a 1 in place of 0 and get "b", and 2 returns "c".