NumberOfYears

Returns decimal representing number of years between two dates. Accurately accounts for leap year without adding .25 days for non leap years.

Average rating: 4.8 (24 votes) Log in to vote

Lance Hallberg
TradeSchool Inc.
http://www.tradeschoolinc.com

Sample input:

NumberOfYears ( Date( 12; 1; 2015 ) , Date( 1; 1; 2018 ) )

Sample output:

2.0876712328767123

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

Let(
	[
		d1	= date1;
		d2	= date2;

		/* Days accumulated in the year of each date */
		daysY1	= ( Date( 12; 31; Year( d1 ) ) + 1 ) - d1;
		daysY2	= d2- ( Date( 1; 1; Year( d2 ) ) - 1 );

		/* Total number of days in year of each date */
		totalY1	= ( Date( 12; 31; Year( d1 ) ) + 1 ) - Date( 1; 1; Year( d1 ) );
		totalY2	= ( Date( 12; 31; Year( d2 ) ) + 1 ) - Date( 1; 1; Year( d2 ) );

		/* Percentage of years accumulated in the year of each date */
		pY1		= daysY1 / totalY1;
		pY2		= daysY2 / totalY2
	];

	Case(
		/* If the dates are in the same year dived the number of days in the year by the difference between the two dates */
		Year( d1 ) = Year( d2 ); ( d2 - d1 + 1 ) / totalY1;

		/* If the two dates are exact month and day, subtract the year of date 1 from the year of date 2 */
		Month( d1 ) = Month( d2 ) and Day( d1 ) = Day( d2 ); Year( d2 ) - Year( d1 );

		/* If the first year is less than the second find the year difference, subtract 1, and add the two year percentages */
		Year( d1 ) < Year( d2 ); ( Year( d2 ) - 1 ) - Year( d1 ) + pY1 + pY2
	)	
)

This function returns the decimal number of years between two dates. It accurately accounts for leap years without adding .25 days onto non leap years.

Comments

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

NumberOfYears

Comments

Support this website.

Support this website.