AnsweredAssumed Answered

Calculating the difference between two dates

Question asked by BarryIrvine on Oct 24, 2011
Latest reply on Oct 25, 2011 by ChristianNorland


Calculating the difference between two dates



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";
        $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)));
    If (gift_aid_yesno;
        donation_amount + (donation_amount * (20 / (100 - 20)));