1 2 Previous Next 17 Replies Latest reply on Jul 29, 2013 9:49 AM by philmodjunk

    Age at 1st Feb Calculation help please

    appt

      Title

      Age at 1st Feb Calculation help please

      Post

           I realise I'm using up my request here but anyway....

           After entering a student's date of birth I then need to calculate their age at 1st of Feb of the current year.

           Anyone got 5 mins to help me on this one?

           Andy

        • 1. Re: Age at 1st Feb Calculation help please
          philmodjunk

               Standard age calc goes something like this:

               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.

               We can modify that to use the date for February 1st of the current year instead of today's date to get what you need:

               Let (  [ B = YourTable::BirthDateField ;
                           T = Date ( 2 ; 1 ; Year ( Get ( CurrentDate ) ) ; 
                        ] ;
                           Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
                     )

          • 2. Re: Age at 1st Feb Calculation help please
            appt

                 Thanks once again Phil.

                 However, being new to scripting I found that the let function was very complex to understand - I mean why does mine have squiggly lines and yours does not?

                 So I just pasted your code in as shown in the screen shot but when I try to close it gives an error message saying there are too many parameters?

            • 3. Re: Age at 1st Feb Calculation help please
              LaRetta_1

                   Remove the semi-colon being selected (right after Get ( CurrentDate )

                   The end of your variable declarations does not require another semi-colon before that final ]

              • 4. Re: Age at 1st Feb Calculation help please
                appt

                     Awesome helps guys. 

                     Through a very slow process of trying to learn and understand the LET function I recreated the code from scratch and realised there was an extra semi-colon and a missing right bracket.

                     If anyone has time to explain the actual maths that would be a bonus - for example what does (2 ; 1 ; Year ( Get ( CurrentDate )) actually represent?

                     Anyway this following code (if anyone else finds it useful) works perfectly

                     Let (  [B = DOB ; T = Date ( 2 ; 1 ; Year (Get ( CurrentDate ) ) ) ] ; Year ( T ) - Year ( B )  - ( T< Date ( Month ( B ) ; Day ( B ) ; Year ( T ) )) )

                     Many thanks

                • 5. Re: Age at 1st Feb Calculation help please
                  schamblee

                       (2;1;Year(Get(CurrentDate)) represent February the 1 of the current year.

                  • 6. Re: Age at 1st Feb Calculation help please
                    philmodjunk

                         More specfiically, DATE ( (2;1;Year(Get(CurrentDate)) represents February the 1 of the current year.

                         The Date, Year and Get functions are all functions that you can look up in FileMaker Help in order to learn more about them.

                         Thanks to LaRetta for spotting the extra ; -- I seem to do that a lot when I put together a Let with the [] option.blush

                    • 7. Re: Age at 1st Feb Calculation help please
                      LaRetta_1

                           Phil said, "Thanks to LaRetta for spotting the extra ; -- I seem to do that a lot when I put together a Let with the [] option"

                           So do I, Phil, that's why I easily spotted it.  ;-)

                      • 8. Re: Age at 1st Feb Calculation help please
                        appt

                             Thanks both of you. I am slowly starting to get a handle on it. I assume that when writing code you have to use month/day notation rather than day/month?

                             The first part are the 2 variables (equal to the 2 years, DOB year and current year), the calculation then takes the DOB year from the current year so if DOB was 2004 and current year was 2013 the result would be 9.

                             Can someone explain the rest of the equation and the use of ; in that equation?

                             For example if I wanted the answer as the age in years + months how would the equation change?

                             Thanks.

                              

                        • 9. Re: Age at 1st Feb Calculation help please
                          philmodjunk

                               As I mentioned earlier, Date is a Function you can look up in FileMaker Help. As a function you pass three parameters to it and the order of these parameters cannot be changed: Date ( Month ; Day ; Year ). The function takes those three values and returns a an item of type Date.

                               The Year function simply extracts the year portiion of a date. If you subtract the year for this year from a person's birth date, you get either their age in years or you get an value that is 1 too many. The difference is whether the person's birthday has been reached in the current year or not.

                               T< Date ( Month ( B ) ; Day ( B ) ; Year ( T ) )

                               Compares today's date (T) to the date of the person's birthday in the current year. The date for this year's birthday is computed by using the date function with the Month of the person's birthdate, the day of their birthdate, but then uses the year of today's date. This "comparison" is called a "Boolean expression" boolean expression use comparison operators such as <, > and = as well as logical operators such as "and", "or", "not"... they evaluate to one of two results "True" or "False". When such a result is used in a number calculation, True values evaluate as 1 and false values evaluate as 0.

                               Thus, this expression subtracts one from the difference of the two years if today's date is before (less than) this years birthday and subtracts zero if it is not.

                                

                          • 10. Re: Age at 1st Feb Calculation help please
                            appt

                                 Cheers Phil,

                                 I'm actually sat here right now with 3 pages of help open as well as my code trying to work it out for myself so that I dont have to keep asking so many questions!

                            • 11. Re: Age at 1st Feb Calculation help please
                              appt

                                   So I have got to this point

                                   Let ( [ T = Date ( 2 ; 1 ; Year ( Get ( CurrentDate ) ) ) ] ; T - Year ( DOB ) - If ( T <  Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( T ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( 2 - Month ( DOB ) + 12 - If ( 1 < Day ( DOB ) ; 1 ; 0 ) ; 12 ) ) & " Months"

                                    

                                   If I put in a DOB of 1st Feb 2004 I get the following result of the calculation (result is set as text in options)

                                    

                                   8/08/2007 Years, 0 Months

                                    

                                   Grrrrrrrrrrrrr

                                    

                              • 12. Re: Age at 1st Feb Calculation help please
                                philmodjunk

                                     It really helps to format your calcs to make them easier to read:

                                     Let ( [ T = Date ( 2 ; 1 ; Year ( Get ( CurrentDate ) ) ) ] ;

                                                T - Year ( DOB ) - If ( T <  Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( T ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( 2 - Month ( DOB ) + 12 -
                                                       If ( 1 < Day ( DOB ) ; 1 ; 0 ) ; 12 )

                                     ) & " Months"

                                     T in your calculation is a date. That means that it stores the number of days from 12/31/0000 to 2/1/2013 (using MMDDYYYY notation).

                                     Subtracting the year of the DOB makes no sense here. I think you intended to use:

                                     Year ( T ) - Year ( DOB ) for that part of the calculation.

                                     And you should select "Text" as your calculation's result type, not Date.

                                • 13. Re: Age at 1st Feb Calculation help please
                                  LaRetta_1

                                       Your calculation doesn't work at all for me, Phil.  And it does not work well when you step outside of using date functions in calculating dates.

                                  Andy, if you want years and months, try this:

                                       Let ( [
                                       next = Date ( Month ( DOB ) + 1 ; Day ( DOB ) ; Year ( DOB ) ) ;
                                       end = Get ( CurrentDate ) ;
                                       startlen = Day ( Date ( Month ( DOB ) + 1 ; 0 ; Year ( DOB ) ) ) ;
                                       x = 12 * ( Year ( end ) - Year ( DOB ) ) + Month ( end ) - Month ( DOB ) - ( Day ( end ) < Day ( DOB ) ) ;
                                       y = Div ( x ; 12 ) ;
                                       m = Mod ( x ; 12 )  ;
                                       string = Case ( y ; y & " year" & Case ( y  ≠ 1 ; "s" ) & ", " ) &
                                       Case ( m ; m & " month" & Case ( m  ≠ 1 ; "s" ) & ", " ) 
                                       ] ;
                                       LeftWords ( string ; Length ( string ) )
                                       ) 
                                        
                                  ... and if you want years, months and days it would be changed to:
                                        
                                                 Let ( [
                                                 next = Date ( Month ( DOB ) + 1 ; Day ( DOB ) ; Year ( DOB ) ) ;
                                                 end = Get ( CurrentDate ) ;
                                                 startlen = Day ( Date ( Month ( DOB ) + 1 ; 0 ; Year ( DOB ) ) ) ;
                                                 x = 12 * ( Year ( end ) - Year ( DOB ) ) + Month ( end ) - Month ( DOB ) - ( Day ( end ) < Day ( DOB ) ) ;
                                                 y = Div ( x ; 12 ) ;
                                                 m = Mod ( x ; 12 )  ;
                                                 d = Case ( Day ( DOB ) > Day ( end )  ; startlen +  Day ( end )  - Day ( DOB )  ; Day ( end ) - Day ( DOB ) ) ;
                                                 string = Case ( y ; y & " year" & Case ( y  ≠ 1 ; "s" ) & ", " ) &
                                                 Case ( m ; m & " month" & Case ( m  ≠ 1 ; "s" ) & ", " ) &
                                                 Case ( d > 0 ; d & " day" & Case ( d  ≠ 1 ; "s" ) )
                                                 ] ;
                                                 LeftWords ( string ; Length ( string ) )
                                                 )

                                                 If you always want to know the age on Feb 1 of the current year then change the end variable from Get ( CurrentDate ) to Date ( 2 ; 1 ; Year ( Get ( CurrentDate ) ) )

                                        

                                  • 14. Re: Age at 1st Feb Calculation help please
                                    philmodjunk

                                         LaRetta, I don't follow this statement: "Your calculation doesn't work at all for me, Phil"

                                         It's not my calculation unless you are referring to the standard "age" calculation that only computes years in my original post.

                                         I've simply pointed out errors in Andy Evan's attempt at setting up a calculoation that computes months and years.

                                    1 2 Previous Next