Customize Weekends With Excel WORKDAY Function

If you’re trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later). It’s more flexible than the older WORKDAY function – it doesn’t assume that you work Monday to Friday any more!

Get the Project End Date

To use the basic features of the WORKDAY.INTL function, you can enter 2 pieces of information:

• project start date
• number of days required to complete the project

Then, WORKDAY.INTL will calculate the working date that is the specified number of after the start date.

In the screen shot below, we start on Thursday, December 10th (cell C8), and the project will take 2 days (cell C10).

The WORKDAY.INTL formula in cell C12 is:

=WORKDAY.INTL(C8,C10)

The table below shows why the result is December 14th. We work on the project on Thursday and Friday (2 days). Since we didn’t specify weekend days, it assumes that we don’t work on Saturday or Sunday. The next working day is Monday, December 14th, so that is the formula result.

Adjust the End Date

I’d rather see the date that the project will be completed, instead of the next working day. So, to adjust the result, I subtract 1 from the number of working days in the formula.

=WORKDAY.INTL(C8,C10-1)

That changes the formula result to Friday, December 11th – the day we stop working on the project.

Exclude Holidays

Just like the older WORKDAY function, you can create a list of holidays, and tell the WORKDAY.INTL function to exclude those dates.

If you use a named Excel table, it’s easy to add or remove dates, when necessary. In the screen shot below, the table is named tblHol, and the date column is used as the Holiday List.

To exclude those holidays from the date calculations, I’ll add that reference in the fourth argument of the function.

=WORKDAY.INTL(C8,C10-1,,tblHol[Date])

With that adjustment, if we start a project on Thursday, December 24th, the end date will be Monday, December 28th.

Here is the table that shows the working days, with the holidays highlighted in orange.

Specify Non-Working Days

If you don’t specify which days are the weekend, the WORKDAY.INTL function automatically excludes Saturday and Sunday as weekend (non-working) days. However, you can select different days as the weekend, with either of the following methods:

• select from the drop down list of options
• create a string with 1 and 0 characters

Select from Drop Down List

The easier way to specify weekend days is to select from the drop down list of options. The list will appear automatically when you start the 3rd argument in the function.

NOTE: If the list doesn’t appear, press Alt + Down Arrow

If I choose option 2 – Sunday, Monday – the project end date changes to Tuesday, December 29th.

=WORKDAY.INTL(C8,C10-1,2,tblHol[Date])

Create a Non-Working Day String

If none of the options in the drop down list are what you need, you can create your own string.

• The string must be 7 digits, representing the weekdays, from Monday to Sunday.
• Use a 0 for working days
• Use a 1 for non-working days

For example, if you only work Monday, Wednesday and Friday, use the string 0101011

I’ll change the start date to a working date in our new schedule – Wednesday, December 23rd. Here is the revised formula, with the 7-digit string in the 3rd argument, inside double quote marks:

=WORKDAY.INTL(C8,C10-1,”0101011″,tblHol[Date])

With those changes, the end date is Monday, December 28th.

Calculate the Non-Working String

If you want to make it easier to select the non-working days, you can use a table, like the one below. Mark the non-working days with an X, and use an IF formula to show 1 or 0 in each row.

=IF(K8=”x”,1,0)

Then, use a CONCATENATE formula to compile the string:

=CONCATENATE(M8,M9,M10,M11,M12,M13,M14)

In the WORKDAY.INTL formula, refer to that cell in the 3rd argument.

=WORKDAY.INTL(C8,C10-1,M15,tblHol[Date])

The calculation columns could be hidden, to prevent people from messing up the formulas.

Notes About WORKDAY.INTL

A few things to note about working with the WORKDAY.INTL function:

• Make sure the start date is a working day, or the results will be different from what you expect.
• You can enter a negative number for days, to calculate a project start date, based on a known end date.
• If you enter a non-integer value for the number of days, it will be truncated.
• You must have at least one zero in the Weekend string – it can’t be 1111111

Are there any other things to watch out for when working with this function?

Download the Sample File

You can download the sample file, to follow along with this tutorial. Go to the Excel Sample Files page on my Contextures website. In the Functions section, look for FN0043 – Calculate Project End Date with WORKDAY.INTL.

The zipped file is in xlsx format, and does not contain macros.

___________________