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.

Xls.YEARFRAC.pq

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))