Business day, next or previous

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
  • xlf-previous-next
    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}\)

xlf-business-next-stay-v2
Fig 2: RD (Wednesday) = BD – step back one BD → PBD (Tuesday) = BD; step forward one BD → NBD (Wednesday) = BD
xlf-business-next-sat-mon
Fig 3: RD (weekend Saturday) = NBD – step back one BD → PBD (Friday) = BD; step forward one BD → NBD (Monday) = BD
xlf-business-next-stay-EM-Tue
Fig 4: RD (Easter Monday) = NBD – step back one BD → PBD (Thursday) = BD; step forward one BD → NBD (Tuesday) = BD

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}\)

xlf-business-previous-stay-v2
Fig 5: RD (Wednesday) = BD – step forward one BD → NBD (Thursday) = BD; step back one BD → PBD (Wednesday) = BD
xlf-business-next-sun-fri
Fig 6: RD (weekend Sunday) = NBD – step forward one BD → NBD (Monday) = BD; step back one BD → PBD (Friday) = BD
xlf-business-previous-EM-Thur
Fig 7: RD (Easter Monday) = NBD – step forward one BD → NBD (Tuesday) = BD; step back one BD → PBD (Thursday) = BD

  • Last modified: 3 Sep 2015, 11:49 am [Australian Eastern Standard Time (AEST)]