If you're using Excel to calculate dates, it is useful to know how to add (or subtract) a certain number of working or business days to a date. This lesson introduces the WORKDAY() function and shows you how to use it.

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. Some tasks end on weekends, and the project end date is earlier than it should be (see below).

Because of this, you need to adjust your formulas so that they are based on working days only, and skip weekends and holidays when working out when a task will finish.

The WORKDAY function

The WORKDAY function allows you to take a date and add a supplied number of working days. By default, the WORKDAY function will ignore weekends, i.e. Saturdays and Sundays. You can also exclude holidays by supplying a range of cells that contain holiday dates.

The syntax of the WORKDAY function is as follows:

=WORKDAY(start_date,working_days,[holiday_range])

These values are fairly self explanatory:

• 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.
• working_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.
• [holiday_range] is one or more dates that should also be skipped when adding the working_days value.

Note that [holiday_range] is optional and can be omitted if you don't need it.

Example of the WORKDAY function in action.

The Workday function is simple to use. Here's the example from earlier with the correct end dates for the individual tasks and the project itself. Column E shows the formula from column D, so you can see what's happening. Notice how the end date of the project is now 28 May - ten days later than the original example.

Using the WORKDAY function to adjust for holidays

As already mentioned, you can extend the WORKDAY function to take account of holidays. The easiest way to do this is to put your holiday dates into a separate column and refer to those dates from inside your formula. This makes it easy to create a list of dates for the whole year and re-use them in all your WORKDAY formulas. Here's an example that uses US holiday dates for 2017.

Here's a new version of the earlier example which uses these dates to calculate the correct task and project end dates after taking into account any of the holidays in this list:

As you can see from column E in this example, the formula used in column E references the table of US holidays, and adjusts the end dates for each task accordingly.

Troubleshooting the WORKDAY function

The main issue you are likely to encounter with the WORKDAY function is with the holiday table. The key is to make sure you have entered your dates correctly. If you copy and paste holiday dates from another source, you may find that they are pasted as text rather than as dates.