Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions

Excel WORKDAY function

The Excel WORKDAY function returns a date that is a given number of working days ahead of or prior to the start date. It excludes weekends as well as any holidays you specify.

The WORKDAY function is purposed for calculating workdays, milestones and due dates based on the standard working calendar, with Saturday and Sunday being the weekend days.

WORKDAY is a built-in function in Excel 2007 - 365. In earlier versions, you need to enable the Analysis ToolPak.

When using WORKDAY in Excel, you have to input the following arguments:

The first 2 arguments are required and the last one is optional:

• Start_date - the date from which to start counting weekdays.
• Days - the number of workdays to add to / subtract from start_date. A positive number returns a future date, a negative number returns a past date.
• Holidays - an optional list of dates that should not to be counted as working days. This can be either a range of cells containing the dates you want to exclude from calculations, or an array constant of the serial numbers representing the dates.

Now that you know the basics, let's see how you can use the WORKDAY function in your Excel worksheets.

Excel WORKDAY.INTL function

WORKDAY.INTL is a more powerful modification of the WORKDAY function that works with custom weekend parameters. As well as WORKDAY, it returns a date that is a specified number of workdays in the future or in the past, but lets you determine which days of the week should be considered weekend days.

The WORKDAY.INTL function was introduced in Excel 2010 and so is not available in earlier Excel versions.

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

The first two arguments are required and are akin to WORKDAY's:

Start_date - the initial date.

Days - the number of working days before (negative value) or after (positive value) the start date. If the days argument is supplied as a decimal number, it is truncated to the integer.

The last two arguments are optional:

Weekend - specifies which weekdays should be counted as weekend days. This can be either a number or a string, as demonstrated below.

Weekend string - a series of seven 0's and 1's that represent seven days of the week, beginning with Monday. 1 represents a non-working day and 0 represents a workday. For example:

• "0000011" - Saturday and Sunday are weekends.
• "1000001" - Monday and Sunday are weekends.

At first sight, weekend strings may seem superfluous, but I personally like this method better because you can make a weekend string on the fly without having to remember any numbers.

Holidays - an optional list of dates you want to exclude from the working day calendar. This can be a range of cells containing the dates, or an array constant of the serial values representing those dates.

Excel NETWORKDAYS function

The NETWORKDAYS function in Excel returns the number of workdays between two dates, excluding weekends and, optionally, the holidays you specify.

The syntax of Excel NETWORKDAYS is intuitive and easy-to-remember:

The first two arguments are obligatory and the third one is optional:

• Start_date - initial date from which to start counting working days.
• End_date - the end of the period for which you are counting workdays.

Both the start date and end date are counted in the returned number of workdays.

• Holidays - an optional list of holidays that should not to be counted as work days.

Excel NETWORKDAYS.INTL function

Like NETWORKDAYS, Excel's NETWORKDAYS.INTL function calculates the number of weekdays between two dates, but lets you specify which days should be counted as weekend days.

The syntax of the NETWORKDAYS.INTL function is very similar to NETWORKDAYS', except it has the additional [weekend] parameter that indicates which days of the week should be counted as weekends.

The weekend argument can accept either a number or a string. The numbers and weekend strings are exactly the same as in the weekend parameter of the WORKDAY.INTL function.

The NETWORKDAYS.INTL function is available in Excel 365 - 2010.

How to highlight workdays in Excel

Using the WORKDAY and WORKDAY.INTL functions, you can not only calculate workdays in your Excel worksheets but also highlight them as your business logic requires. For this, you create a conditional formatting rule with either a WORKDAY or WORKDAY.INTL formula.

For example, in a list of dates in column B, let's highlight only future dates that are within 15 workdays from today's date, excluding two holidays in cells A2:A3. The most obvious formula that comes to mind is as follows:

=AND($B2>TODAY(), $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3))

The first part of the logical test cuts off past dates, i.e. you check if a date is equal to or greater than today: $B2>TODAY(). And in the second part, you verify whether a date is no more than 15 weekdays in the future, excluding the weekend days and specified holidays: $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3)

The formula looks correct, but once you create a rule based on it, you will realize that it highlights wrong dates:

Let's try to figure out why that happens. The problem is not with the WORKDAY function, as someone may conclude. The function is right, but... what does it actually do? It returns a date 15 workdays from now, excluding weekend days (Saturday and Sunday) and holidays in cells A2:A3.

Okay, and what does the rule based on this formula do? It highlights ALL the dates that are equal to or greater than today and less than the date returned by the WORKDAY function. You see? All the dates! If you don't want to color the weekends and holidays, then you need to explicitly tell Excel not to. So, we are adding two more conditions to our formula:

• The WEEKDAY function to exclude weekends: WEEKDAY($B2, 2)<6
• The COUNTIF function to exclude holidays: COUNTIF($A$2:$A$3, $B2)=0

As demonstrated in the below screenshot, the improved formula works perfectly:

=AND($B2>TODAY(), $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3), COUNTIF($A$2:$A$3, $B2)=0, WEEKDAY($B2, 2)<6)