Emulate the DAYS360 function (1)
Emulate the Excel DAYS360 function with method = False [U.S. (NASD)]
- US NASD method - decision sequence
- If Date1 is EOM and Date1 is last day of February and Date2 is last day of February, then D2 = 30
- If Date1 is EOM and Date1 is last day of February, then D1 = 30
- If D2 is 31, and D1 is 30 or 31, then D2 = 30
- If D1 is 31, then D1 = 30
- NASD: The National Association of Securities Dealers (NASD) merged with the Financial Industry Regulatory Authority (FINRA) on 30 July 2007.
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
- This example was developed in Excel 365 :: VBA 7.1
- Published: 16 June 2020
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]