Excel functions WORKDAY and NETWORKDAYS to calculate working days (2023)

This short tutorial explains how to use the Excel functions NETWORKDAYS and WORKDAY to calculate working days with custom weekend and holiday parameters.

Microsoft Excel offers two functions designed specifically for calculating the days of the week - WORKDAY and NETWORKDAYS.

ÖWORKDAYThe function returns a date N business days in the future or in the past, and you can use it to add or subtract business days from a given date.

Use ofNETWORK DAYSFunction allows you to calculate the number of working days between two specified dates.

Starting with Excel 2010, more powerful modifications of the above WORKDAY.INTL and NETWORKDAYS.INTL functions are available that allow you to define which and how many days are weekend days.

Now let's look at each function and see how you can use it to calculate the working days in your Excel spreadsheets.

• Excel WORKDAY function - add or subtract workdays
• WORKDAY.INTL - calculates workdays with custom weekends
• Excel NETWORKDAYS - counts the working days between 2 dates
• NETWORKDAYS.INTL - counts weekdays between 2 dates with custom weekends
• How to mark working days in Excel

Excel's WORKDAY function

Excel's WORKDAY function returns a date a specified number of working days before or before the start date. Excludes weekends and any public holidays you specify.

The WORKDAY function is designed to calculate work days, milestones, and due dates based on the default work calendar, with Saturday and Sunday being the weekend days.

WORKDAY is a built-in feature in Excel 2007 – 365. In earlier versions, you must enable itAnalysis Tool Package.

When using WORKDAY in Excel, you must enter the following arguments:

WORKDAY(start_date, days, [holidays])

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

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

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

How to use WORKDAY to add/subtract past workdays

Follow these simple rules to calculate working days in Excel:

• To theAddBusiness days, enter a positive number, e.gdiamArgument of a WORKDAY formula.
• To thesubtractBusiness days, use a negative number in thediamStreit.

Suppose you have a start date in cell A2, a list of holidays in cells B2:B5, and you want to find the dates 30 business days in the future and in the past. You can do this with the following formulas:

To add 30 business days to the start date excluding holidays in B2:B5:

=DIA DE TRABALHO(A2, 30, B2:B5)

To subtract 30 business days from the start date excluding holidays in B2:B5:

=DIA DE TRABALHO(A2, -30, B2:B5)

To calculate the days of the week based on thecurrent date, use the TODAY() function as the start date:

To add 30 business days to today's date:

=WORKDAY(TODAY(), 30)

To subtract 30 business days from today's date:

=WORKDAY(TODAY(), -30)

To enter the start date directly into the formula, use the DATE function:

=WORKDAY(DATE(2015,5,6),30)

The screenshot below shows the results of all these and a few other WORKDAY formulas:

And of course you can enter the number of working days to add/subtract from the start date in a cell and then refer to that cell in your formula. For example:

=WORKDAY(A2, C2)

Where A2 is the start date and C2 is the number of non-weekend days after (negative numbers) or before (positive numbers) the start date, not excluding holidays.

Excel-Funktion WORKDAY.INTL

WORKDAY.INTL is a more powerful modification of the WORKDAY function that works withcustom weekend parameters🇧🇷 Like WORKDAY, it returns a date that is a specified number of workdays in the future or in the past, but you can determine which days of the week should be considered weekend days.

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

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

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

The first two arguments are required and are similar to WORKDAY:

start date- the start date.

Dias- the number of business days before (negative value) or after (positive value) the start date. If thediamIf the argument is specified as a decimal, it is truncated to the integer.

The last two arguments are optional:

weekend- specifies which days of the week should be counted as weekend days. It can be a number or a string as shown below.

weekend episode- a series of seven zeros and ones representing seven days of the week, starting with Monday. 1 stands for a non-working day and 0 for a working day. For example:

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

Weekend strings may seem superfluous at first, but I personally like this method best because it's a quick way to create a weekend string without having to remember any numbers.

public holidays- an optional list of dates you want to exclude from the weekday calendar. This can be a range of cells containing data, or an array constant of serial values ​​representing that data.

Using WORKDAY.INTL in Excel - Formula Examples

Well, the sheer amount of theory we just discussed can seem quite complicated and confusing, but trying out formulas will make things really easy.

In our dataset, with the start date in cell A2 and a list of holidays in A5:A8, let's calculate working days with custom weekends.

To theAdd30 business days for the start date, Friday and Saturday are considered as weekends and holidays in A5:A8 except:

=DIA DE TRABALHO.INTL(A2, 30, 7, A5:A8)

or

=WERKTAG.INTL(A2, 30, "0000110", A5:A8)

To thesubtract30 working days from the start date, Sunday and Monday are considered weekends and holidays in A5:A8 except:

=DIA DE TRABALHO.INTL(A2, -30, 2, A5:A8)

or

=ARBEITSTAG.INTL(A2, -30, "1000001", A5:A8)

To add 10 working dayscurrent date, Sunday is the only day of the weekend, excluding holidays:

=WORKDAY.INTL(TODAY(), 10, 11)

or

=WORKDAY.INTL(A2, 10, "0000001")

In the Excel worksheet, the formulas could look like this:

Monitoring.The Excel functions WORKDAY and WORKDAY.INTL return serial numbers that represent dates. To display these numbers as a date, select the cells with the numbers and pressCtrl+1to open theformat cellsDialogue. NopeNumbertab, selectDatumnoCategoryList and select the desired date format. For detailed steps, seeHow to change date format in Excel.

Error WORKDAY and WORKDAY.INTL for Excel

If the Excel formula WORKDAY or WORKDAY.INTL returns an error, it's probably for one of the following reasons:

#NUM!An error occurs when:

• a combination ofstart dateediamArguments results in an invalid date or
• weekendThe argument in the WORKDAY.INTL function is invalid.

#BRAVERY!An error occurs when:

• start dateor any value inpublic holidaysis not a valid date, is it
• diamArgument is not numeric.

Excel's WORKDAYS function

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

The Excel NETWORKDAYS syntax is intuitive and easy to remember:

NETWORKDAYS(start_date, end_date, [holidays])

The first two arguments are required and the third is optional:

• start date- Start date from which counting of working days begins.
• data final- the end of the period for which you count working days.

Both the start date and the end date are included in the number of working days returned.

• public holidays- an optional list of public holidays not to be counted as working days.

How to use NETWORK DAYS in Excel - Formula example

Suppose you have a list of holidays in cells A2:A5, start dates in column B, end dates in column C, and you want to know how many business days are between these dates. The correct NETWORK DAYS formula is easy to find out:

=NETWORKDAYS(B2, C2, $A$2:$A$5)

Note that the Excel NETWORKDAYS function returns a positive value if the start date is less than the end date, and a negative value if the end date is less than the start date (as in line 5):

NETWORKDAYS.INTL function from Excel

FocusedNETWORK DAYS, the Excel function NETWORKDAYS.INTL calculates the number of days of the week between two dates, but lets you specify which days to count as weekend days.

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

NETWORKDAYS.INTL(start date, end date, [weekend], [holidays])

ÖweekendThe argument can accept a number or a string. Weekend numbers and strings are exactly the same as inweekendparameters ofWORKDAY.INTL function.

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

Using NETWORKDAYS.INTL in Excel - Formula example

Using the list of dates from the previous example, let's calculate the number of working days between two dates, where Sunday is the only weekend day. To do this, enter the number 11 in theweekendArgument of your NETWORKDAYS.INTL formula or create a string of six zeros and one one ("0000001"):

=NETZTAGE.INTL(B2, C2, 11, $A$2:$A$5)

or

=NETZTAGE.INTL(B2, C2, "0000001", $A$2:$A$5)

The screenshot below proves that both formulas return absolutely identical results.

How to mark working days in Excel

With the WORKDAY and WORKDAY.INTL functions, you can not only calculate working days in your Excel spreadsheets, but also highlight them according to your business logic. To do this, create oneConditional formatting rulewith a WORKDAY or WORKDAY.INTL formula.

For example, in a date list, in column B, we only highlight future dates that are within 15 business days of today's date, excluding two holidays in cells A2:A3. The most obvious formula that comes to mind is the following:

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

The first part of the logic test truncates past dates, meaning you check if a date is equal to or greater than today: $B2>TODAY(). And in the second part, check if a date is no more than 15 business days in the future, excluding certain weekends and holidays:$B2<=WORKDAY(TODAY(), 15, $A$2:$A$3)

The formula looks correct, but if you create a rule based on it, you'll find that it highlights the wrong dates:

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

Ok, and what does the rule based on this formula do? Highlights ALL dates equal to or greater than today and less than the date returned by the WORKDAY function. Do you see? All appointments! If you don't want to color weekends and holidays, you must explicitly tell Excel not to do so. So let's add two more conditions to our formula:

• ÖWEEK functionto exclude weekends: WEEKDAY($B2, 2)<6
• ÖCOUNTIF functionto exclude holidays: COUNTIF($A$2:$A$3, $B2)=0

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

=E($B2>HOJE(), $B2<=DIATRABAL(HOJE(), 15, $A$2:$A$3), CONT.SE($A$2:$A$3, $B2)=0, DIA DA SEMANA ($B2, 2) <6)

As you can see, the WORKDAY and WORKDAY.INTL functions make calculating workdays in Excel quick and easy. Of course, your real-world formulas are probably more sophisticated, but knowing the basics helps immensely because you can only remember a small amount of essential stuff and infer the rest. Thanks for reading and we hope to see you on our blog next week!

You might also be interested in

• Using the MONTH and EOMONTH functions in Excel
• Calculate the number of weeks using Excel's WEEKNUM function
• Subtract date, add days, weeks, months and years to date