3 Replies Latest reply on Nov 8, 2012 2:40 PM by EndreN.Vasstrand

    What is the syntax of the function Yearfrac (start date; end date)?

    EndreN.Vasstrand

      Title

      What is the syntax of the function Yearfrac (start date; end date)?

      Post

           I am quite new to FileMaker and unexperienced. So far I have not been able to add the right parameters to get the range between to dates eg start and end. In Excel this function is  =YEARFRAC(date start;date end). The format of date is dd.mm.yyyy (European). Would be really gratefull for help. ENVasstrand

        • 1. Re: What is the syntax of the function Yearfrac (start date; end date)?
          philmodjunk

               Dates are stored internally as numbers.Yearfrac would appear to be a custom function as it is not listed in Help, but if it is written correctly, the date format does not matter as long as the fields used for the parameters are of type date, or if you use calculations in place of fields, they are expressions that return a date data type.

               You may need to post an example of how you are using this function in a calculation.

          • 2. Re: What is the syntax of the function Yearfrac (start date; end date)?
            philmodjunk

                 My first response seems to have vanished into thin air--a brand new bug apparently. If you see this post twice, you'll know why:

                 This function cannot be found in FileMaker help, thus I must assume that it is a custom function.

                 Assuming proper design methods were used to create the custom function the format won't matter, the data type will.

                 Dates are stored internally as numbers counting the number of days since 12/31/0000. Thus the format does not matter so long as you use a field or expression that evaluates as a date. Make sure to  use either fields of type date or expressions that specify the date data type as parameters in your call to this function.

                 GetAsDate ( "12/31/2012" )

                 Date ( 12 ; 31 ; 2012 )

                 are two functions that return date types and thus can be handy if used in calculations that need dates.

            • 3. Re: What is the syntax of the function Yearfrac (start date; end date)?
              EndreN.Vasstrand

                   It seems I have figured it out:

                    

                   Case(
                    
                   IsEmpty (Date2);
                    
                   " " ;
                    
                   (Date2 - Date1)/365
                   )
                    
                   This returns the years elapsed and ignores the calculation where Date2 is empty.