AnsweredAssumed Answered

Calculating the difference between two dates

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

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))

Outcomes