xlf EandA series

VBA functions :: Days Count, Actual, European and NASD methods

This is a code development module, providing some code logic ideas, presented here as an exercise.

0. Preliminary

The EXERCISE - implement the following

Function procedure

Write three VBA Function procedures to count the number of days between two days. Use day count methods of Actual, Days 360 European and US (NASD)
Syntax:

1. xlfDays(end_date, start_date). This is similar to the WS DAYS function

2. xlfDays360euro(start_date, end_date). This is similar to the WS DAYS360 function using the European method

3. xlfDays360NASD(start_date, end_date). This is similar to the WS DAYS360 function using the US NASD method

Each xlfDays... function returns the number of days (nights) between the start_date and end_date. The order of the parameters matches the Excel WS functions.

Each function has the following arguments:

end_date Required. An ISO 8601 date of the form yyyymmdd as a number
start_date Required. An ISO 8601 date of the form yyyymmdd as a number

Your code statements should be limited mostly to mathematical operators, logic statements, and code control procedures. Do not use any Excel or VBA Date and Time functions

1. The code

The following material is based on coding techniques covered presented 90045.

1.1 A version of Days Actual function

This code is based on xlfDateToSerialNumber with the addition of nested loops.

Code 1: the xlfDays function. An interpretation of the set task

Function xlfDays(end_date As Long, start_date As Long) As Long
' Syntax: xlfDays(end_date, start_date)
' Description: Returns the number of days between two dates (equivalent to WS DAYS function)
' Arguments: end_date required - entered as yyyymmdd, a number. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
'            start_date required - entered as yyyymmdd, a number. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
' Return value is type long. xlfDays includes the 29 February 1900 leap year error.
' If date arguments are invalid, xlfDays returns the Error Value -1
' Reference: https://support.microsoft.com/en-us/office/days-function-57740535-d549-4395-8728-0f07bff0b9df
' 14 October 2022, excelatfinance.com
' =================
Dim Arg(1 To 2) As Long
Dim Y(1 To 2) As Integer, M(1 To 2) As Integer, D(1 To 2) As Integer
Dim Days(1 To 2) As Long, DaysInMth(1 To 2) As Integer, MthDays(1 To 2) As Integer
Dim i As Integer, j As Integer
Dim Epoch As Long: Epoch = 19000101

Arg(1) = end_date: Arg(2) = start_date

' Dte integrity ===
For i = 1 To 2
    If Len(CStr(Arg(i))) <> 8 Then GoTo ErrHandler ' CStr conversion resolves 4 digit error with 99991231
        Y(i) = Left(Arg(i), 4): M(i) = Mid(Arg(i), 5, 2): D(i) = Right(Arg(i), 2)
    If Not (Y(i) >= 1900 And Y(i) <= 9999) Or _
       Not (M(i) >= 1 And M(i) <= 12) Or _
       Not (D(i) >= 1 And D(i) <= 31) Then GoTo ErrHandler
Next i
' Years ===========
    For i = 1 To 2
        For j = Left(Epoch, 4) To Y(i) - 1
        If Not (j Mod 4 = 0 And (j Mod 100 <> 0 Or j Mod 400 = 0)) Then
            Days(i) = Days(i) + 365
        Else
            Days(i) = Days(i) + 366
        End If
        Next j
    Next i
' Months ==========
    'If M > 1 Then
    For i = 1 To 2
        If M(i) > 1 Then
            For j = Mid(Epoch, 5, 2) To M(i) - 1
                Select Case j
                Case 1, 3, 5, 7, 8, 10, 12
                    DaysInMth(i) = 31
                    MthDays(i) = MthDays(i) + DaysInMth(i)
                Case 2
                    If Not (Y(i) Mod 4 = 0 And (Y(i) Mod 100 <> 0 Or Y(i) Mod 400 = 0)) Then
                        DaysInMth(i) = 28
                        MthDays(i) = MthDays(i) + DaysInMth(i)
                    Else
                        DaysInMth(i) = 29
                        MthDays(i) = MthDays(i) + DaysInMth(i)
                    End If
                Case 4, 6, 9, 11
                    DaysInMth(i) = 30
                    MthDays(i) = MthDays(i) + DaysInMth(i)
                End Select
            Next j
        End If
    Next i
    'End If
    For i = 1 To 2
        Days(i) = Days(i) + MthDays(i)
    Next i
' Days ============
    For i = 1 To 2
        Days(i) = Days(i) + D(i)
    Next i
    ' Include 1900 error
    For i = 1 To 2
        If Arg(i) > 19000228 Then Days(i) = Days(i) + 1
    Next i
' Return value ====
    xlfDays = Days(1) - Days(2)
Exit Function

ErrHandler:
    xlfDays = -1
End Function

1.2 A version of the Days 360 EURO function

Includes a Variant subtype error handler.

Code 2: the xlfDays360euro function. An interpretation of the set task

Function xlfDays360euro(start_date As Variant, end_date As Variant) As Variant
' Syntax: xlfDays360euro(start_date, end_date)
' Description: Returns the number of days between two dates (equivalent to XL.WS DAYS360 European function)
' Arguments: end_date required - entered as yyyymmdd, a number or text. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
'            start_date required - entered as yyyymmdd, a number or text. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
' Return value is type long.
' If date arguments are invalid, xlfDays360euro returns the Error #NUM!
' Date: 14 October 2022, excelatfinance.com
' =================
Dim Arg(1 To 2) As Long
Dim Y(1 To 2) As Integer, M(1 To 2) As Integer, D(1 To 2) As Integer
Dim tmp As Long, i As Integer

On Error GoTo ErrHandler2
' Determine Variant subtype
If TypeName(start_date) = "Long" Or _
    TypeName(start_date) = "String" Or _
    TypeName(start_date) = "Range" Then Arg(1) = start_date
If TypeName(end_date) = "Long" Or _
    TypeName(end_date) = "String" Or _
    TypeName(end_date) = "Range" Then Arg(2) = end_date

' Dte integrity ===
For i = 1 To 2
    If Len(CStr(Arg(i))) <> 8 Then GoTo ErrHandler1 ' CStr conversion resolves 4 digit error with 99991231
        Y(i) = Left(Arg(i), 4): M(i) = Mid(Arg(i), 5, 2): D(i) = Right(Arg(i), 2)
    If Not (Y(i) >= 1900 And Y(i)  <= 9999) Or _
       Not (M(i) >= 1 And M(i)  <= 12) Or _
       Not (D(i) >= 1 And D(i)  <= 31) Then GoTo ErrHandler1
Next i

' Euro ============
With Application.WorksheetFunction
    D(1) = .Min(D(1), 30)
    D(2) = .Min(D(2), 30)
End With

' Return value ====
    tmp = (Y(2) - Y(1)) * 360 + (M(2) - M(1)) * 30 + (D(2) - D(1))
    xlfDays360euro = tmp    ' return as subtype Long
Exit Function

ErrHandler1:
    xlfDays360euro = VBA.CVErr(xlErrNum)    ' error number 2036
Exit Function
ErrHandler2:
    xlfDays360euro = VBA.CVErr(xlValue)     ' error number 2015
End Function

1.3 A version of the Days 360 NASD function

Another version of code 3 NASD method is available at Emulate the DAYS360 function.

Code 3: the xlfDays360NASD function. An interpretation of the set task

Function xlfDays360NASD(start_date As Long, end_date As Long) As Variant
' Syntax: xlfDays360NASD(start_date, end_date)
' Description: Returns the number of days between two dates (equivalent to XL.WS DAYS360 NASD function)
' Arguments: end_date required - entered as yyyymmdd, a number or text. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
'            start_date required - entered as yyyymmdd, a number or text. ISO 8601 (basic format) _
'    19000101 to 99991231. "Start_date and End_date are the two dates between which you _
'    want to know the number of days."
' Return value is type long.
' If date arguments are invalid, xlfDays360NASD returns the Error #NUM!
' Date: 14 October 2022, excelatfinance.com
' =================
Dim Arg(1 To 2) As Long
Dim Y(1 To 2) As Integer, M(1 To 2) As Integer, D(1 To 2) As Integer
Dim tmp As Long, i As Integer

Arg(1) = start_date: Arg(2) = end_date

' Dte integrity ===
For i = 1 To 2
    If Len(CStr(Arg(i))) <> 8 Then GoTo ErrHandler ' CStr conversion resolves 4 digit error with 99991231
        Y(i) = Left(Arg(i), 4): M(i) = Mid(Arg(i), 5, 2): D(i) = Right(Arg(i), 2)
    If Not (Y(i) >= 1900 And Y(i) <= 9999) Or _
       Not (M(i) >= 1 And M(i) <= 12) Or _
       Not (D(i) >= 1 And D(i) <= 31) Then GoTo ErrHandler
Next i


' NASD ============
    If D(1) = 31 Then D(1) = 30
    If D(2) = 31 And D(1) >= 30 Then D(2) = 30
    If D(2) = 31 And D(1) < 30 Then D(2) = 1: M(2) = M(2) + 1

' Return value ====
    tmp = (Y(2) - Y(1)) * 360 + (M(2) - M(1)) * 30 + (D(2) - D(1))
    xlfDays360NASD = tmp    ' return as subtype Long
Exit Function

ErrHandler:
    xlfDays360NASD = VBA.CVErr(xlErrNum)     ' error number 2015
End Function

1.2 Procedure testing

The testing platform is worksheet based. Performance is slow because of the large number of formulas, and some including extensive loops.

References

ExcelAtFinance (2019), VBA, is year a leap year. [Accessed 18 October 2022]
ExcelAtFinance (2020), Emulate the DAYS360 function (1). [Accessed 18 October 2022]
Github (2022), ISDA day counters. Python code. [Accessed 18 October 2022]
Quantlib-1.28 (2022), Quantlib-1.28 > ql > time > daycounters. C++ .cpp code. [Accessed 25 October 2022]

Download the Excel file for this module: xlfDaysCount.xlsm [408 KB]
Development platform: Microsoft Excel for Microsoft 365 (Version 2211 Build 16.0.15822.20000) 64-bit and VBA 7.1
Published: 18 October 2022
Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Time (AET)]