Excel YEARFRAC QB Forumla Equivalent

Mark's code is a simple and efficient approximation of the YEARFRAC function that I suspect will work in most situations. If you are looking for a replica of the YEARFRAC function that will return the same result as Excel, I ported the code below from the logic detailed at https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html. To use the formula in your app, create a "Formula - Numeric" field and change the values of the two variables at the top of the formula to match your table's start/end date fields. Note that the formula only supports the "US (NASD) 30/360" basis, which is the default day count basis for Excel.

// Modify the fields below according to your table.
var Date StartDate = [Start Date];
var Date EndDate = [End Date];

//
// DO NOT NOT MODIFY BELOW THIS LINE
//
// Adapted from https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html
//

var Number StartMonth = Month($StartDate);
var Number StartYear = Year($StartDate);
var Bool StartDateIsEOM = Day($StartDate) = Day(LastDayOfMonth($StartDate));

var Number StartDay = If(
  (Day($StartDate) = 31) or ($StartMonth = 2 and $StartDateIsEOM), 30,
  Day($StartDate)
);

var Number EndMonth = Month($EndDate);
var Number EndYear = Year($EndDate);
var Bool EndDateIsEOM = Day($EndDate) = Day(LastDayOfMonth($EndDate));

var Number EndDay = If(
($EndMonth = 2 and $EndDateIsEOM) and ($StartMonth = 2 and $StartDateIsEOM), 30,
  Day($EndDate) = 31 and Day($StartDate) >= 30, 30,
  Day($EndDate)
);

((($EndYear - $StartYear) * 360) + (($EndMonth - $StartMonth) * 30) + ($EndDay - $StartDay)) / 360

------------------------------
Chris Pliakas
------------------------------