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:
To subtract 30 business days from today's date:
To enter the start date directly into the formula, use the DATE function:
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:
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.
Tipp.In Excel 365 and 2021, you can use WORKDAY in combination withORDERto generate oneseries of working days.
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 the
diamIf 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.
|1 or omitted||Saturday Sunday|
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)
=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)
=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)
=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 of
diamArguments results in an invalid date or
weekendThe argument in the WORKDAY.INTL function is invalid.
#BRAVERY!An error occurs when:
start dateor any value in
public 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 in
weekendparameters 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 the
weekendArgument 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)
=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
What is the difference between WORKDAY and Networkdays in Excel? ›
The WORKDAY function returns a date N working days in the future or in the past and you can use it to add or subtract workdays to a given date. Using the NETWORKDAYS function, you can calculate the number of workdays between two dates that you specify.How do I use the WORKDAY function in Excel? ›
WORKDAY is a function in Excel that calculates the date that is x days after a given date. The function takes two arguments: the date that you want to find the days since, and the number of days. For example, if you want to find the date that is 7 days after June 1st, you would use the function =WORKDAY(6/1,7).How do I calculate the number of working days between two dates in Excel? ›
Use the NETWORKDAYS.INTL function when you want to calculate the number of workdays between two dates. You can also have it exclude weekends and holidays too. Before you begin: Decide if you want to exclude holiday dates. If you do, type a list of holiday dates in a separate area or sheet.How do you calculate the number of working days? ›
- Count the number of days between the two dates.
- Subtract the number of weekend days between the two dates.
Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays.How does Networkdays formula work? ›
NETWORKDAYS calculates the number of work days between two dates. To calculate the working day a specific number of days ahead of a date, use WORKDAY . NETWORKDAYS works similarly to NETWORKDAYS. INTL but only treats Saturday and Sunday as the weekend.How do you calculate working days in Excel excluding weekends and holidays? ›
Enter the NETWORKDAYS Function
The NETWORKDAYS function returns the number of whole working days between a start_date and end_date. Working days exclude weekends and any dates identified in holidays.
Description. Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.How do I use Networkdays? ›
The syntax of the NETWORKDAYS function is “=NETWORKDAYS(Start Date, End Date, Holidays).” The NETWORKDAYS function accepts the following arguments: Start date – This is the start date that you wish to use in the calculation of working days. This value is always entered as a serial date and not as a text date.How do I create a work schedule in Excel? ›
- Open a blank spreadsheet. Start by searching for the Excel application on your computer or clicking on the Excel icon on your desktop. ...
- Create a header. ...
- Title your columns with weekdays. ...
- List employee names in each row. ...
- Enter shift details. ...
- Save your schedule.
Is there an Excel formula to calculate days? ›
=DAYS (end_date, start_date)
The function requires two arguments: Start_date and End_date. They are the two dates between which we wish to calculate the number of days.
Click on the Time Worklet. Scroll down and click Enter Time on the bottom left corner of the screen. Click Run Calculations. Click Confirm in the middle of the screen.How do you check if a day is a WORKDAY in Excel? ›
We can use the WORKDAY function to know if a day is a workday or not. This function calculates defined “workdays” dates in the future or past. It automatically excludes weekend dates and (optionally), holidays.How do you calculate working days in Excel excluding weekends and holidays in Excel? ›
The NETWORKDAYS function in Excel returns the number of workdays between two dates, excluding weekends and, optionally, the holidays you specify. The first two arguments are obligatory and the third one is optional: Start_date - initial date from which to start counting working days.How do I calculate working days in Excel excluding Sundays? ›
Count days excluding Sundays with formula
Here I can introduce a formula for counting days excluding Sundays during a date range. Select a blank cell, here is C2, and type this formula =B2-A2-INT((B2-A2-WEEKDAY(B2)+1)/7) into it, and then press Enter key, a date displayed.