3 Replies Latest reply on Aug 27, 2013 6:40 AM by philmodjunk

    Calculating with dates

    AlarS.

      Title

      Calculating with dates

      Post

           Hello!

           Lots of googleing showed that this is very common problem but still couldn't find a solution for my problem. Basically I have a database which tracks aircraft service bulletins. I track it by flight hours and dates. With flight hours I got it working but with dates it's complicated.

           I've got these fields:

      Recurrent (text) - for selecting if the bulletin is recurrent or not (Y or N)
      Date (date) - date when the bulletin is applicable
      PerformedC (date) - date when the bulletin was performed
      IntervalY (number) - interval how often the bulletin has to be performed again (eg. 0.5 = every 6 months)
      NextC (date) - calculates next date when the bulletin has to be performed

           What I need is a NextC field to calculate the next maintenance date. I tried using this calculation, but it results in jibburish:

           NextC = If ( Recurrent = "N" and PerformedC ≥ Date; "N/A"; If ( PerformedC = ""; Date + IntervalY * 365; PerformedC + IntervalY * 365))

           The next thing I need is to calculate the number of days left to the next maintenance:

           Fields are the mainly the same + LeftD. I used this calculation:

           LeftD = If ( PerformedC = "" or "N/A"; NextC - Date  ; NextC -  PerformedC )

           P.S. These calculations are correct, checked them in Excel, all it needs is to handle with dates correctly.

            

           If anyone could help or lead me to right direction, I couldn't be more thankful.

            

           Best Regards,

           Alar

        • 1. Re: Calculating with dates
          philmodjunk

               Like Excel, dates in FileMaker are stored as a number representing the number of days since an early arbitrary date and adding a number of days to a given date computes a new date that many days in the future. Thus your syntax for NextC and LeftD look correct.

               I'd check to make sure that your date fields are really of type date (and that your calculation fields specify date as the result type)--not text.

          • 2. Re: Calculating with dates
            AlarS.

                 Thank you, got that problem sorted out.

                 One thing that bugs me is with NextC calculation (modified it a bit when comparing to the first post):
                 If ( Recurrent = "Y" ; If ( PerformedC ≠ "" ; PerformedC + IntervalY * 365 ; Date + IntervalY * 365) ; If ( PerformedC ≠ "" ; "N/A" ; Date + IntervalY * 365 ))

                 Problem is with the bold part of the calculation. Basically when the bulletin is not recurrent (Recurrent ≠ "Y") and is performed (PerformedC ≠ "") it should say N/A (not applicable). BUT... since the NextC is defined as a Date field and FileMaker doesn't recognise it as a date, it only shows a question mark instead of "N/A".

                 Is there a way to get the field to display "N/A"?

            • 3. Re: Calculating with dates
              philmodjunk

                   Not and have the value returned as a date. Either convert the computed date into text: GetAsText ( Yourdate calculation here ) or define a separate field that returns the N/A. (The N/A field can be transparent and placed on top of the other field so it looks like one field to the user.) You can also place the layout text "N/A" on the layout and use conditional formatting to render it invisible when the date calculation is not empty.