Emulate the DAYS360 function


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


VBA :: xlfDAYS360NASD_v1


Following the decision sequence 1 to 4 above, code 1 emulates the DAYS360 function in the case where start_date < end_date.



Code 1: UDF xlfDAYS360NASD_v1 for forward looking day basis
Function xlfDAYS360NASD_v1(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
' version 1: start_date < end_date
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_v1 = (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