1 Reply Latest reply on Oct 25, 2011 7:54 AM by ChristianNorland

    Calculating the difference between two dates

    BarryIrvine

      Title

      Calculating the difference between two dates

      Post

      Hi,

      I have a Donation table where it is possible to specify that the donation type is "Standing Order". In order to calculate the total donation amount I need to calculate the number of whole months since the donation started. At the moment I haven't put an end date in there (I'll need to add that soon) so I calculate it based on today.

      My automatically calculated field works quite well but I just wondered if this was the most efficient way of doing it or whether there was a better way of calculating the date difference in months between two dates in Filemaker?

      Here is the function behind my total_donation_amount field:

      Case (
          donation_type = "Pledge" ; 0 ;
          donation_type = "Standing Order";
      Let(
          [
              $today = Get(CurrentDate);
              $today_year = Year($today);
              $today_month = Month($today);
              $today_day = Day($today);
              $year = Year(donation_date);
              $month = Month(donation_date);
              $day = Day(donation_date);
              $datediff = (($today_year - $year)*12 + ($today_month - $month) - If ($day>$today_day; 1; 0));
              $total = donation_amount * $datediff
          ]
      ;    If (gift_aid_yesno;
              $total + ($total * (20 / (100 - 20)));
              $total))
      ;
          If (gift_aid_yesno;
              donation_amount + (donation_amount * (20 / (100 - 20)));
              donation_amount))