9 Replies Latest reply on Sep 3, 2013 4:34 PM by philmodjunk

    How do I calculate non-rounded whole elapsed years?

    basilisk2

      Title

      How do I calculate non-rounded whole elapsed years?

      Post

           I have a date and need to calculate how many whole years have elapsed since that time. I don't want the months or days, just the number of whole years as an integer (so that I can add '1' to the result to give the number of the next anniversary eg 2 full elapsed years + 1 = "Your next anniversary will occur on the (date of anniversary) and will be your third anniversary".

           Can you please help? I tried doing a Calculation but  couldn't find "Today" or even Get (CurrentDate) which is referenced all through these forums but is not in my FMPA 11 help guide or function list.

           All suggestions welcomed!

        • 1. Re: How do I calculate non-rounded whole elapsed years?
          philmodjunk

               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 non-rounded whole elapsed years?
            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 non-rounded whole elapsed years?
              philmodjunk

                   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 non-rounded whole elapsed years?
                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 non-rounded whole elapsed years?
                  philmodjunk

                       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 non-rounded whole elapsed years?
                    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 non-rounded whole elapsed years?
                      philmodjunk

                           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 non-rounded whole elapsed years?
                        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?) semi-colon 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 non-rounded whole elapsed years?
                          philmodjunk

                               Good catch on the missing semi-colon, 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".