Add working days to a date in Excel with non-standard weekend days

The WORKDAY() function in Excel allows you to add working days to a date. It allows you skip over weekend days. However, it assumes your weekend days are Saturday and Sunday. If your weekends fall on different days, or you need a different number of days, you need the WORKDAY.INTL() function instead, which lets you define different options for weekend days.

Scenario

You have a project spreadsheet in which you have a number of tasks, each of which will take a certain number of working days to complete. You need to calculate the completion date of each task and of the overall project. You can do this by adding the duration of each task to the end date of the previous task but, when you do this, your results include weekends, so the dates are wrong. You try to use the WORKDAY() function, but that function assumes that weekend days are Saturday and Sunday, and you need to use different days.

The WORKDAY.INTL function

The WORKDAY.INTL function allows you to take a date and add a supplied number of working days. It will allow you to specify which days are weekend days. Once specified, calculations perfomed using the WORKDAY.INTL function will skip over the days you have identified as weekend days. You can also exclude holidays by supplying a range of cells that contain holiday dates.

The syntax of the WORKDAY.INTL function is as follows:

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

Let's look at each of these in turn:

• the start_date value is a date. You can type this into your formula but it's probably easier to refer to another cell that contains the date you want.
• days is the number of working days you want to add or subtract to the start date. Note that this number can be positive to add working days, or negative to subtract working days.
• [weekend] is a number or string that identifies your weekend days. We'll see the options below. This is an optional value, but if you don't provide it, your weekend days will be Saturday and Sunday, and you may as well use the WORKDAY() function instead.
• [holidays] is one or more dates that should also be skipped when adding the working_days value. This is also optional.

Defining your weekend days

The weekend value in the WORKDAY.INTL() function allows you to define your weekend days in just about any way you want.

There are two options when entering the weekend value:

1. Provide a number from a pre-defined list that identifies which day(s) are weekend days.
2. Provide a 7-digit string of 1's and 0's that identify which days of the week are weekend days. For example, "0000011" would indicate that your weekend days are Saturday and Sunday.

Choosing your weekend days from a pre-defined list

This is the simplest of the two methods for choosing your weekend days. All you have to do is find the number from the list that matches your weekend days. However, it is also limited compared to the second method, as you'll see from the list:

This method doesn't allow for all possibilities. Your options are limited to 1 or 2 weekend days per week, but your 2-day options are all contiguous, i.e. two days together. For more options, you need to turn to the second method below.

Defining your weekend days manually using a string of 1's and 0's

This method gives you complete flexibility when choosing your weekend days. You can specify any days, and any number of days. All you have to do is decide which days are weekends, and then provide a string that matches it.

Here are some examples:

The final example is an unlikely example of weekend days. However, if you are trying to add working days for someone who only work part time, the last example might be just what you need.