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

Calculating the difference between two dates

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
]
\$total + (\$total * (20 / (100 - 20)));
\$total))
;