When using @BusinessDays the other day, I decided to go through and test multiple scenarios and document what @BusinessDays would return. This formula, introduced in Notes 6, returns the number of business days between two dates. It can also be used with multiple date values to return the business days between each pair of dates, but that won't be discussed here. The big feature with this function is the ability to exclude specific days of the week. So even though there's actually 4 days between Friday and Monday, there's only 2 business days.

All these examples are going under the assumption that Saturday and Sunday are the days of the week to exclude. The day numbers (in a list) is the third parameter to the function, so any days can be excluded. But all the examples use 1:7 (1 = Sunday, 7 = Saturday) just to make it easier for everyone to follow.

My references below to the "weekend" mean Saturday or Sunday (the days I exclude) and "during the week" mean Monday through Friday (the days that are not excluded).

When No Holidays Are Included

The @BusinessDays function takes up to 4 parameters. The fourth parameter is a list of dates to specifically exclude (holidays). This parameter is optional. The first set of observations are all made when this parameter is missing.

• When the start date is later than the end date, by 1 day or 1,000,000 days, @BusinessDays will return -1.


• When the start date and end date are the same day, AND the date is during the week, @BusinessDays will return 1.
• When the start date and end date are the same day, AND the date is on the weekend, @BusinessDays will return 0.


• When the start date and end date are consecutive days, and both dates are during the week, @BusinessDays will return 2.
• When the start date and end date are consecutive days, and the start date is during the week, and the end date is on the weekend, @BusinessDays will return 1.
• When the start date and end date are consecutive days, and the start date is on the weekend, and the end date is during the week, @BusinessDays will return 1.
• When the start date and end date are consecutive days, and both dates are on the weekend, @BusinessDays will return 0.

From those observations, you should be able to extrapolate the other values. For example, if the start date is a Monday and the end date is the following Friday (no weekend crossing), then @BusinessDays is 5. You move the end date forward one day (to Saturday) or two days (to Sunday) and you still have 5. Move it forward three days (to Monday) and it returns 6.

So, probably the biggest observation is that both the start date and the end date are included during the calculation. Think of it as the start of business on the start date to the end of business on the end date. If they are consecutive days during the week, that's two full business days.

When Holidays Are Included

This set of observations includes holidays. Note that you can include one or more holidays (the fourth parameter is a list).

• If there is no holiday in the list within the start and end dates, you can refer to all the "non-holiday" rules above.


• When the start date and end date are the same day, and the date is one of the holidays, @BusinessDays will return 0.


• When the start date and end date are consecutive days, and the start date is during the week, and the end date is one of the holidays, @BusinessDays will return 1.
• When the start date and end date are consecutive days, and the start date is one of the holidays, and the end date is during the week, @BusinessDays will return 1.


• When the start date and end date are consecutive days, and the start date is one of the holidays, and the end date is on the weekend, @BusinessDays will return 0.
• When the start date and end date are consecutive days, and the start date is on the weekend, and the end date is one of the holidays, @BusinessDays will return 0.

Again, think of it in terms of the start of business on the start date and the end of business on the end date. If one of the holidays is a Tuesday and the start date is the day before (Monday) and the end date is the day after (Wednesday), then that's two full business days (all day Monday and all day Wednesday) so @BusinessDays will return 2.

Hopefully this list of observations helps you out the next time you want to use @BusinessDays in your application.