# Excel YEARFRAC function for Power Query

While there is a native YEARFRAC-function in DAX that you can use to calculate year fractions, sometimes you might just need it in Power Query. The function I’m presenting here has the same function arguments than its Excel-equivalent. Its 5 different modes require some advanced calculations. Fortunately I found a great resource on how to calculate it here. So my version here is basically a direct translation into the M-language.

### The code for the Excel YEARFRAC function for Power Query

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.

 let func = let //Algo source: https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html FIsLeapYear = (Year) => if Number.Mod(Year, 4) > 0 then false else if Number.Mod(Year, 100) > 0 then true else if Number.Mod(Year, 400) = 0 then true else false, FIsEndOfMonth = (Day, Month, Year) => if List.Contains({1, 3, 5, 7, 8, 10, 12}, Day) then 31 else if List.Contains({4, 6, 9, 11}, Day) then 30 else if FIsLeapYear(Year) then 29 else 28, Days360 = (StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) => ((EndYear – StartYear) * 360) + ((EndMonth – StartMonth) * 30) + (EndDay – StartDay), TmpDays360Nasd = (StartDate, EndDate, Method, UseEom) => let StartDay = Date.Day(StartDate), StartMonth = Date.Month(StartDate), StartYear = Date.Year(StartDate), EndDay = Date.Day(EndDate), EndMonth = Date.Month(EndDate), EndYear = Date.Year(EndDate), Select = [ EndDay = if (EndMonth = 2 and FIsEndOfMonth(EndDay, EndMonth, EndYear)) and ( (StartMonth = 2 and FIsEndOfMonth(StartDay, StartMonth, StartYear)) or Method = 3 ) then 30 else if EndDay = 31 and (StartDay >= 30 or Method = 3) then 30 else EndDay, StartDay = if StartDay = 31 then 30 else if ( UseEom = 2 and StartMonth = 2 and FIsEndOfMonth(StartDay, StartMonth, StartYear) ) then 30 else StartDay ], TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) in #"TmpDays360Nasd", TmpDays360Euro = (StartDate, EndDate) => let StartDay = Date.Day(StartDate), StartMonth = Date.Month(StartDate), StartYear = Date.Year(StartDate), EndDay = Date.Day(EndDate), EndMonth = Date.Month(EndDate), EndYear = Date.Year(EndDate), StartDay_ = if (StartDay = 31) then 30 else StartDay, EndDay_ = if (EndDay = 31) then 30 else EndDay, TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay_, EndDay_) in TmpDays360Euro, TmpDiffDates = (StartDate, EndDate, Basis) => if Basis = 0 then TmpDays360Nasd(StartDate, EndDate, 0, true) else if List.Contains({1, 2, 3}, Basis) then Duration.Days(EndDate – StartDate) else TmpDays360Euro(StartDate, EndDate), TmpCalcAnnualBasis = (StartDate, EndDate, Basis) => if List.Contains({0, 2, 4}, Basis) then 360 else if Basis = 3 then 365 else let StartDay = Date.Day(StartDate), StartMonth = Date.Month(StartDate), StartYear = Date.Year(StartDate), EndDay = Date.Day(EndDate), EndMonth = Date.Month(EndDate), EndYear = Date.Year(EndDate), TmpCalcAnnualBasis_ = if (StartYear = EndYear) then if FIsLeapYear(StartYear) then 366 else 365 else if ((EndYear – 1) = StartYear) and ( (StartMonth > EndMonth) or ((StartMonth = EndMonth) and StartDay >= EndDay) ) then if FIsLeapYear(StartYear) then if StartMonth < 2 or (StartMonth = 2 and StartDay <= 29) then 366 else 365 else if FIsLeapYear(EndYear) then if EndMonth > 2 or (EndMonth = 2 and EndDay = 29) then 366 else 365 else 365 else List.Accumulate( {StartYear .. EndYear}, 0, (state, current) => if FIsLeapYear(current) then state + 366 else state + 365 ), TmpCalcAnnualBasis__ = TmpCalcAnnualBasis_ / (EndYear – StartYear + 1) in TmpCalcAnnualBasis__, Result = (StartDate, EndDate, Basis_) => let Basis = if Basis_ = null then 0 else Basis_, nNumerator = TmpDiffDates(StartDate, EndDate, Basis), nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis), TmpYearFrac = nNumerator / nDenom in TmpYearFrac in Result , documentation = [ Documentation.Name = " Xls.YEARFRAC.pq ", Documentation.Description = " Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Equivalent of the YEARFRAC-Function in Excel. ", Documentation.LongDescription = " Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term. Equivalent of the YEARFRAC-Function in Excel. ", Documentation.Category = " Xls.Date ", Documentation.Source = " www.TheBIcountant.com – https://wp.me/p6lgsG-2t4 . ", Documentation.Version = " 1.0 ", Documentation.Author = " Imke Feldmann ", Documentation.Examples = {[Description = " ", Code = " let Start_date = #date(2012,1,1) , End_date = #date(2012, 7, 30), Basis = 3, FunctionCall = Xls_YEARFRAC(Start_date, End_date, Basis) in FunctionCall ", Result = " 0.578082191780821 "]}] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Xls.YEARFRAC.pq

hosted with ❤ by GitHub

### Function parameters

The Excel YEARFRAC function for Power Query has the same parameters than its Excel equivalent:

YEARFRAC(start_date, end_date, [basis])

The YEARFRAC function syntax has the following arguments:

• Start_date    Required. A date that represents the start date.
• End_date    Required. A date that represents the end date.
• Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4

European 30/360

If you are interested in more Power Query functions that replicate Excel functions who haven’t made it into the M-language (yet?) please check out this collection.

Enjoy and stay queryious 😉