3 Replies Latest reply on Oct 9, 2016 3:29 PM by keywords

    Date Field Calculations

    dominicjoannou

      Hi All,

       

      I have tried the suggestion below with:

       

      If (IsEmpty (Date of Referral to Regulatory Body);  "";  (Date of Interim Sanction) - (Date of Referral to Regulatory Body)) or

       

      Case(

        not IsEmpty(dateReferall) and not IsEmpty(dateFinal), dateFinal - dateReferral;

        not IsEmpty(dateReferall) and not IsEmpty(dateInterim); dateInterim - dateReferral;

        GetAsNumber("")

       

      I have tried:

       

      Let command instead of Case(

       

      I have also tried reclassifying the fields as already suggested.

       

      Through the command line in blue, the field of Date of Referral to Regulatory Body is ignored.

       

      However, a lot of people go directory onto their Final Sanction after referral. They do not have an Interim Sanction. This leaves the Interim Sanction field blank.

       

      Where I have managed the suggestions in italics and reclassifying fields, it comes up with 0 and 1; which is useless to me, because I am trying to measure time.

       

      I use Filemaker 13 Pro. Maybe I am asking too much from this package and the options are cleaning the data in either Excel or Stata.

       

      Many thanks

        • 1. Re: Date Field Calculations
          David Moyer

          Hi,

          the problem may be

          (Date of Interim Sanction) - (Date of Referral to Regulatory Body)

          This will evaluate to 1-1, 1-0, 0-1 or 0-0.  Please try it without the parentheses:

          Date of Interim Sanction - Date of Referral to Regulatory Body

           

          Also, I believe that if you just add on to your previous threads on this topic, it would be easier for folks to follow along and help.

          • 2. Re: Date Field Calculations
            David Moyer

            my bad, I had that burned into my brain at some point.

            The parentheses aren't the problem.

            sorry

            • 3. Re: Date Field Calculations
              keywords

              Try this calc:

               

              Let (

              [

                dREF = "10/10/2016" // substitute your field name here

              ; dINT = "17/10/2016" // and here

              ; dFIN = "24/10/2016" // and here

              ; result = If (  not IsEmpty ( dINT ) ; GetAsDate ( dFIN ) - GetAsDate ( dINT ) ; GetAsDate ( dFIN ) - GetAsDate ( dREF ) )

              ] ;

              result & " days"

              )

               

              The result line will give you the period between referral and interim if there is an interim date, but otherwise will give the period between referral and final date. If the data you feed in is in date fields you will not need to use GetAsDate, but it will make no difference if you do.