Excel's YEARFRAC algorithm

[Doug Mahugh <Doug.Mahugh@microsoft.com>]

David,

 

Attached are two programs that demonstrate the algorithm Excel uses for the YEARFRAC function.  Note that these are not actual source code from Excel, but rather sample programs that we have put together to help the formula subcommittee understand Excel’s implementation better.  As we discussed in Monday’s call, the goal in sharing these is to help you verify that the algorithm you’ve created in Python matches what Excel is doing for YearFrac.

 

Just FYI, I want to be careful to set expectations appropriately going forward, since I can’t be certain that I’ll always be able to share this type of sample for other functions we may discuss in the future.  My goal is to help the formula subcommittee and the broader community understand our implementation whenever possible, but we’ll need to handle these sorts of situations on a case-by-case basis.  I may be able to share sample code like this for other functions, or pseudocode, or a narrative description of our algorithm, but in some cases I may not be able to obtain or provide specific information on a particular algorithm in a timely fashion.  I’ll do my best to provide what’s needed, and in this case I’m able to share these code samples with the TC.  I hope you’ll find them useful, and please let me know if you need anything else from me on this matter.

 

FYI for everyone else on the list, I have joined the ODF TC and the formula subcommittee in the last week as Microsoft’s representative.  I’m traveling through the end of this week but will be back in my office as of next Monday, and I’m looking forward to working with all of you going forward.

 

Best regards,

Doug

 

Doug Mahugh | Senior Product Manager, Office Client Interoperability | 425-707-1182 | blogs.msdn.com/dmahugh

 

Public Function FIsLeapYear(Year As Integer) As Boolean
    If (Year Mod 4) > 0 Then
        FIsLeapYear = False
    ElseIf (Year Mod 100) > 0 Then
        FIsLeapYear = True
    ElseIf (Year Mod 400) = 0 Then
        FIsLeapYear = True
    Else
        FIsLeapYear = False
    End If
    
End Function

Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean
    Select Case Month
        Case 1, 3, 5, 7, 8, 10, 12
            FIsEndOfMonth = (Day = 31)
        Case 4, 6, 9, 11
            FIsEndOfMonth = (Day = 30)
        Case 2
            If FIsLeapYear(Year) Then
                FIsEndOfMonth = (Day = 29)
            Else
                FIsEndOfMonth = (Day = 28)
            End If
    End Select

End Function



Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer

    Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
End Function


Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    
    StartDay = Day(StartDate)
    StartMonth = Month(StartDate)
    StartYear = Year(StartDate)
    EndDay = Day(EndDate)
    EndMonth = Month(EndDate)
    EndYear = Year(EndDate)
    
    If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then
        EndDay = 30
    End If
    If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then
        EndDay = 30
    End If
    If StartDay = 31 Then
       StartDay = 30
    End If
    If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then
        StartDay = 30
    End If
    TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
    
End Function

Public Function TmpDays360Euro(StartDate As Date, EndDate As Date)
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    
    StartDay = Day(StartDate)
    StartMonth = Month(StartDate)
    StartYear = Year(StartDate)
    EndDay = Day(EndDate)
    EndMonth = Month(EndDate)
    EndYear = Year(EndDate)
    
    If (StartDay = 31) Then
        StartDay = 30
    End If
    If (EndDay = 31) Then
        EndDay = 30
    End If
    TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
End Function

Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer
    
    
    Select Case Basis
        Case 0 'atpmBasis30360
            TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)
        
        Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days
            TmpDiffDates = DateDiff("d", StartDate, EndDate)
                  
        Case 4 'atpmBasisE30360
            TmpDiffDates = TmpDays360Euro(StartDate, EndDate)
    End Select
    
End Function

Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    Dim iYear As Integer
    
    Select Case Basis
        Case 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360
            TmpCalcAnnualBasis = 360
        Case 3 'atpmBasisActual365
            TmpCalcAnnualBasis = 365
        Case 1 ' atpmBasisActual
            StartDay = Day(StartDate)
            StartMonth = Month(StartDate)
            StartYear = Year(StartDate)
            EndDay = Day(EndDate)
            EndMonth = Month(EndDate)
            EndYear = Year(EndDate)
        
            If (StartYear = EndYear) Then
                If FIsLeapYear(StartYear) Then
                    TmpCalcAnnualBasis = 366
                Else
                    TmpCalcAnnualBasis = 365
                End If
            ElseIf ((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
                        TmpCalcAnnualBasis = 366
                    Else
                        TmpCalcAnnualBasis = 365
                    End If
                ElseIf FIsLeapYear(EndYear) Then
                    If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then
                        TmpCalcAnnualBasis = 366
                    Else
                        TmpCalcAnnualBasis = 365
                    End If
                Else
                    TmpCalcAnnualBasis = 365
                End If
            Else
                For iYear = StartYear To EndYear
                    If FIsLeapYear(iYear) Then
                        TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366
                    Else
                        TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365
                    End If
                Next iYear
                TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)
                           
            End If
    End Select
 
End Function


Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)
    Dim nNumerator As Integer
    Dim nDenom As Double
    
    nNumerator = TmpDiffDates(StartDate, EndDate, Basis)
    nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)

    TmpYearFrac = nNumerator / nDenom
End Function

=end VBA source code for YearFrac

#-----------------------------------------------------------------------------
# Ruby version starts here, with VBA code in comment blocks for comparison ...
#-----------------------------------------------------------------------------

Public Function FIsLeapYear(Year As Integer) As Boolean
    If (Year Mod 4) > 0 Then
        FIsLeapYear = False
    ElseIf (Year Mod 100) > 0 Then
        FIsLeapYear = True
    ElseIf (Year Mod 400) = 0 Then
        FIsLeapYear = True
    Else
        FIsLeapYear = False
    End If
    
End Function

Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean
    Select Case Month
        Case 1, 3, 5, 7, 8, 10, 12
            FIsEndOfMonth = (Day = 31)
        Case 4, 6, 9, 11
            FIsEndOfMonth = (Day = 30)
        Case 2
            If FIsLeapYear(Year) Then
                FIsEndOfMonth = (Day = 29)
            Else
                FIsEndOfMonth = (Day = 28)
            End If
    End Select

End Function



Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer

    Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
End Function


Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    
    StartDay = Day(StartDate)
    StartMonth = Month(StartDate)
    StartYear = Year(StartDate)
    EndDay = Day(EndDate)
    EndMonth = Month(EndDate)
    EndYear = Year(EndDate)
    
    If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then
        EndDay = 30
    End If
    If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then
        EndDay = 30
    End If
    If StartDay = 31 Then
       StartDay = 30
    End If
    If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then
        StartDay = 30
    End If
    TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
    
End Function

Public Function TmpDays360Euro(StartDate As Date, EndDate As Date)
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    
    StartDay = Day(StartDate)
    StartMonth = Month(StartDate)
    StartYear = Year(StartDate)
    EndDay = Day(EndDate)
    EndMonth = Month(EndDate)
    EndYear = Year(EndDate)
    
    If (StartDay = 31) Then
        StartDay = 30
    End If
    If (EndDay = 31) Then
        EndDay = 30
    End If
    TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
End Function

Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer
    
    
    Select Case Basis
        Case 0 'atpmBasis30360
            TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)
        
        Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days
            TmpDiffDates = DateDiff("d", StartDate, EndDate)
                  
        Case 4 'atpmBasisE30360
            TmpDiffDates = TmpDays360Euro(StartDate, EndDate)
    End Select
    
End Function

Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double
    Dim StartDay As Integer
    Dim StartMonth As Integer
    Dim StartYear As Integer
    Dim EndDay As Integer
    Dim EndMonth As Integer
    Dim EndYear As Integer
    Dim iYear As Integer
    
    Select Case Basis
        Case 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360
            TmpCalcAnnualBasis = 360
        Case 3 'atpmBasisActual365
            TmpCalcAnnualBasis = 365
        Case 1 ' atpmBasisActual
            StartDay = Day(StartDate)
            StartMonth = Month(StartDate)
            StartYear = Year(StartDate)
            EndDay = Day(EndDate)
            EndMonth = Month(EndDate)
            EndYear = Year(EndDate)
        
            If (StartYear = EndYear) Then
                If FIsLeapYear(StartYear) Then
                    TmpCalcAnnualBasis = 366
                Else
                    TmpCalcAnnualBasis = 365
                End If
            ElseIf ((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
                        TmpCalcAnnualBasis = 366
                    Else
                        TmpCalcAnnualBasis = 365
                    End If
                ElseIf FIsLeapYear(EndYear) Then
                    If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then
                        TmpCalcAnnualBasis = 366
                    Else
                        TmpCalcAnnualBasis = 365
                    End If
                Else
                    TmpCalcAnnualBasis = 365
                End If
            Else
                For iYear = StartYear To EndYear
                    If FIsLeapYear(iYear) Then
                        TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366
                    Else
                        TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365
                    End If
                Next iYear
                TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)
                           
            End If
    End Select
 
End Function


Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)
    Dim nNumerator As Integer
    Dim nDenom As Double
    
    nNumerator = TmpDiffDates(StartDate, EndDate, Basis)
    nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)

    TmpYearFrac = nNumerator / nDenom
End Function

YearFrac.rb