# 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)]