Business day
This module demonstrates how to determine if a date is a business day, and if not return the date of the next or previous business day. The key function is the Excel WORKDAY function.
- SYNTAX: WORKDAY(start_day, days, [holiday])
- where:
- start-day (required): a date that represents the start date
- days (required): the number of business days before or after start_date. A positive value returns a future date; a negative value returns a past date.
- holiday (optional): a list of dates to exclude from the working day calendar
Selected Excel functions used in this module and associated business day functions.
| Excel functions | Description |
|---|---|
| DATEVALUE(year,month,day) | Returns the serial number of a particular date |
| EDATE(start_date, months) | Returns the serial number of a particular date that is a specified number of months before or after the start date |
| EOMONTH(start_date, months) | Returns the serial number of the last day of the month before or after a specified number of months |
| TEXT(value,format_text) | Formats a number and converts it to text. “ddd” returns the first three characters of the day of the week |
| WORKDAY(start_day, days, [holiday]) | Returns the serial number of a particular date that is a specified number of days before or after the start date with optional holiday parameters |
| WORKDAY.INTL(start_date, days, [weekend], [holidays]) | Returns the serial number of the date before or after a specified number of workdays with optional weekend and holiday parameters |
Terminology
See figure 1 for details:
- Reference date: the date being examined
- Business day: not a weekend or holiday
- Next business day – also referred to as the following business day – step positive
- Previous business day – step negative
-
Fig 1: Business days – previous and next with respect to the reference day - In the Stock Analyser project, business days are referred to as trading days
The WORKDAY function
The data set used in the following examples is for the month of March 2016, which includes public holidays for Good Friday (25 March 2016) and Easter Monday (28 March 2016). These dates are in a range named Holiday.
Legend to example figures 2 to 7. The reference date (RD) has a green background. Abbreviations used: BD: Business day, PBD: previous business day, and NBD: next business day.
Business day or next business day
Suppose an analyst has a series of dates returned by the EDATE function. Is the reference date a business day. and if not, what is the date of the next business day.
Applications of business day or next business day include:
- Payment of interest on a bond: if the coupon is due on a non business day, then it is normally paid on the next business day
- A bank accepted bill (BAB) is often written for a term of 90 days, but the issuer of the BAB needs to ensure that the maturity date is a business day. Also, in term of settlements (Source AFMA)
“transactions negotiated before noon are for settlement on the same day and transactions negotiated after noon are for settlement on the following Business Day”
The decision rule is:
\( \begin{cases}
\text{if reference date is a workday}, & \text{return reference date serial number}\\
else \\
\text{if reference date is NOT a workday},& \text{return the date serial number of the NEXT business day}
\end{cases}\)
Business day or previous business day
In this case the decision rule is. Is the reference date a business day. and if not, what is the date of the previous business day.
Applications of business day or previous business day include:
- Calculation of penalties in breach of contract. The number of days in the past where penalties are applied
- Analysis of historical price data sets in business and finance areas
- Dynamic data analysis applications such as the Stock Analyser project
The decision rule is:
\( \begin{cases}
\text{if reference date is a workday}, & \text{return reference date serial number}\\
else \\
\text{if reference date is NOT a workday},& \text{return the date serial number of the PREVIOUS business day}
\end{cases}\)
GetCF function have been converted to text because of the xlsx file format.
*/ ?>