Business day, next or previous


1.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.



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

2. Terminology


See figure 1 for details:

3. 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.


3.1 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:


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

3.2 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:


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