The easiest way to generate a list of business days is using following functions:
1. WORKDAY
2. WORKDAY.INTL
Generating list of business days using WORKDAY:
The WORKDAY function returns the serial number of the date before or after a specified number of workdays.
Syntax: =WORKDAY(start_date, days, holidays)
- Start_date: Needs the input of the start date. This could be directly entered or can be given as cell reference.
- Days: This is the number of non-weekend and non-holiday days before or after the start-date
- Positive value is used to get date after the start-date
- Negative value is used to get number before the start-date
- Holidays: This is an optional argument wherein you can feed the list of additional holidays.
Notes:
- Excel stores the dates as sequential serial numbers for the ease of calculations (by default 1 January 1900 is stored as 1.
- In order to view the number as date, select the cell and follow as below:
- Right click >> Format cells >> "Number" tab >> Select "Date" in Category
- If any of the input date is not a valid date, the function will return #VALUE! error.
- If the output returned is an invalid date, the function returns #NUM! error.
Generate list of business days using WORKDAY.INTL function:
This function is only available in Excel 2010 and 2013 versions.
Syntax: =WORKDAY.INTL(start_date, days, [weekend], [Holidays])
As you would have noticed from above syntax, the only addition is [weekend] as compared to WEEKDAY function syntax.
The two functions are pretty much the same except the WORKDAY.INTL function allows you to specify when your weekend dates are.
Note:
The default weekend is Saturday and Sunday. Hence, if the desired weekend is same, then you can keep this argument blank.
