CF_DateInterval ( FI ; FF )

This function determines the interval between two dates.

Average rating: 2.0 (1 vote) Log in to vote

Carlos Bittar
none
no web site, sorry

Sample input:

CF_DateInterval (04/20/1980; 02/15/2018)

CF_DateInterval (12/31/2017; 02/15/2018)

CF_DateInterval (12/10/2017; 12/14/2018)

Sample output:

37 years 9 months 25 days

1 month 15 days

1 year 4 days

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ( 

[

// break down date 1 (FI)
ai = Year ( FI );
mi = Month ( FI );
di = Day ( FI );

// break down date 2 (FF)
af = Year ( FF );
mf = Month ( FF );
df = Day ( FF );

// calculate years
ac =  af - ai ;

// calculate months
mc = Case ( mi = mf; 0; 
            mi < mf; mf - mi;
            mi > mf; 12-(mi-mf) );
ac = If ( mi > mf; ac-1; ac); 

// calculate days
vr = Case (mi =1; 31; mi = 3; 31; mi = 5; 31; mi = 7; 31; mi = 8; 31; mi = 10; 31; mi = 12; 31; mi = 4; 30; mi = 6; 30; mi = 9; 30; mi = 11; 30; mi = 2; If ( Mod (ai; 4) = 0; 29; 28) );
dc = Case ( di = df; 0;
            di< df; df-di;
            di > df; vr-(di-df) );

// adjusts month and year when both months are identical but days are different
ac = If (di > df and mc = 0; ac-1; ac );
mc = If (di > df and mc = 0; 12-(mi-mf); mc );
mc = If (di > df and mc <> 0; mc-1; mc );

// literal
al = Case ( ac = 0; ""; ac = 1; " year"; ac > 1; " years" );
ml = Case ( mc = 0; ""; mc = 1; " month"; mc > 1; " months" );
dl = Case ( dc = 0; ""; dc = 1; " day"; dc > 1; " days" );

// gathering numbers and letters
ar = If ( ac = 0; ""; ac & al & " " );
mr = If ( mc = 0; ""; mc & ml & " " );
dr = If ( dc = 0; ""; dc & dl );

// putting together all calculated values (years, months, days)
alltogether = ar & mr & dr;

// if both dates, date1 and date2, are the same, show custom message, otherwise shows the result
result = If (FI = FF; "both dates are the same"; alltogether)

];

result

)

This function determines the interval between two dates, even considers leap years. Due to the way both dates of the time interval are broken down, this function can be used in mm/dd/yyyy and dd/mm/yyyy format.
Date 2 must be iqual or greater than Date 1.
Please do not change the order of the calculations, otherwise it won't work.
I hope you'd liked this.

Comments

		19752, fmc Feb 12, 2018
I'd calculate year to day order, so on the first sample it would be 37 years 9 months 26 days. (37 years and 9 month after 4/20/1980 is 1/20/2017, so 2/15/2017 is 26 days after.)

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

CF_DateInterval ( FI ; FF )

Comments

Support this website.

Support this website.