Emulate the DAYS360 function (1)


Emulate the Excel DAYS360 function with method = False [U.S. (NASD)]


VBA :: xlfDAYS360NASD


Following the decision sequence 1 to 4 above, code 1 emulates the DAYS360 function.



Code 1: UDF xlfDAYS360NASD for forward looking day basis
Function xlfDAYS360NASD(start_date As Date, end_date As Date) As Long
' Emulate the Excel DAYS360 function with method = False (U.S. (NASD)) method
' Syntax: DAYS360(start_date,end_date[,method])
' Requires:
'       xlfGetEOFeb
'       xlfGetEOM

Dim Date1 As Date, Date2 As Date
Dim D1 As Integer, D2 As Integer, EOM As Date, EOFeb1 As Date, EOFeb2 As Date
Dim M1 As Integer, M2 As Integer
Dim Y1 As Integer, Y2 As Integer

    Date1 = start_date
    Date2 = end_date

    EOM = xlfGetEOM(Date1)
    EOFeb1 = xlfGetEOFeb(Date1)
    EOFeb2 = xlfGetEOFeb(Date2)

    Y1 = Year(Date1): Y2 = Year(Date2)
    M1 = Month(Date1): M2 = Month(Date2)
    D1 = Day(Date1): D2 = Day(Date2)

    If Date1 = EOM And Date1 = EOFeb1 And Date2 = EOFeb2 Then D2 = 30   ' 1.
    If Date1 = EOM And Date1 = EOFeb1 Then D1 = 30                      ' 2.
    If D2 = 31 And (D1 = 30 Or D1 = 31) Then D2 = 30                    ' 3.
    If D1 = 31 Then D1 = 30                                             ' 4.

    xlfDAYS360NASD = (Y2 - Y1) * 360 + (M2 - M1) * 30 + (D2 - D1)

End Function
' ===========================
Private Function xlfGetEOM(Dte As Date) As Date
    xlfGetEOM = DateSerial(Year(Dte), Month(Dte) + 1, 0)
End Function

' ===========================
Private Function xlfGetEOFeb(Dte As Date) As Date
    xlfGetEOFeb = DateSerial(Year(Dte), 2 + 1, 0)
End Function