by This short tutorial explains the use of Excel NETWORKDAYS and WORKDAY functions to calculate workdays with custom weekend parameters and holidays.
Microsoft Excel provides two functions specially designed for calculating weekdays - WORKDAY and NETWORKDAYS.
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.
In Excel 2010 and higher, more powerful modifications of the above-said functions are available, WORKDAY.INTL and NETWORKDAYS.INTL, which let you define which and how many days are weekend days.
And now, let's have a closer look at each function and see how you can use it to calculate working days in your Excel worksheets.
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.
How to use WORKDAY to add / subtract business days to date
To calculate workdays in Excel, follow these simple rules:
- To add workdays, enter a positive number as the days argument of a WORKDAY formula.
- To subtract workdays, use a negative number in the days argument.
Supposing you have a start date in cell A2, a list of holidays in cells B2:B5, and you want to find out the dates 30 workdays in the future and past. You can do this using the following formulas:
To add 30 workdays to the start date, excluding holidays in B2:B5:
=WORKDAY(A2, 30, B2:B5)
To subtract 30 workdays from the start date, excluding holidays in B2:B5:
=WORKDAY(A2, -30, B2:B5)
To calculate weekdays based on the current date, use the TODAY() function as the start date:
To add 30 workdays to today's date:
=WORKDAY(TODAY(), 30)
To subtract 30 workdays from today's date:
=WORKDAY(TODAY(), -30)
To supply the start date directly to the the formula, use the DATE function:
=WORKDAY(DATE(2015,5,6), 30)
The following screenshot demonstrates the results of all these and a few more WORKDAY formulas:
And naturally, you can enter the number of workdays to add to / subtract from the start date in some 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 behind (negative numbers) or ahead of (positive numbers) the start date, no holidays to exclude.
Tip. In Excel 365 and 2021, you can use WORKDAY in combination with SEQUENCE to generate a series of working days.
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.
| Number | Weekend days |
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
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.
Using WORKDAY.INTL in Excel - formula examples
Well, the pretty big bulk of theory we've just discussed may seem quite complicated and confusing, but trying your hand at formulas will make things really easy.
On our dataset, with the start date in cell A2 and a list of holidays in A5:A8, let's calculate workdays with custom weekends.
To add 30 workdays to the start date, Friday and Saturday counted as weekends and holidays in A5:A8 excluded:
=WORKDAY.INTL(A2, 30, 7, A5:A8)
or
=WORKDAY.INTL(A2, 30, "0000110", A5:A8)
To subtract 30 workdays from the start date, Sunday and Monday counted as weekends and holidays in A5:A8 excluded:
=WORKDAY.INTL(A2, -30, 2, A5:A8)
or
=WORKDAY.INTL(A2, -30, "1000001", A5:A8)
To add 10 workdays to the current date, Sunday being the only weekend day, no holidays:
=WORKDAY.INTL(TODAY(), 10, 11)
or
=WORKDAY.INTL(A2, 10, "0000001")
In your Excel sheet, the formulas may look similar to this:
Note. Both Excel WORKDAY and WORKDAY.INTL functions return serial numbers representing the dates. To get those numbers displayed as dates, select the cells with the numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Date in the Category list, and choose the date format you want. For the detailed steps, please see How to change date format in Excel.
Excel WORKDAY and WORKDAY.INTL errors
If your Excel WORKDAY or WORKDAY.INTL formula returns an error, the reason is likely to be one of the following:
#NUM! error occurs if either:
- a combination of the
start_dateanddaysarguments results in an invalid date, or weekendargument in the WORKDAY.INTL function is invalid.
#VALUE! error occurs if either:
start_dateor any value inholidaysis not a valid date, ordaysargument is non-numeric.
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.
How to use NETWORKDAYS in Excel - formula example
Let's say 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 workdays are between these dates. The appropriate NETWORKDAYS formula is easy to figure out:
=NETWORKDAYS(B2, C2, $A$2:$A$5)
Notice that the Excel NETWORKDAYS function returns a positive value when the start date is less than the end date, and a negative value if the end date is more recent than the start date (as in row 5):
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.
Using NETWORKDAYS.INTL in Excel - formula example
Using the list of dates from the previous example, let's calculate the number of workdays between two dates with Sunday being the only weekend day. For this, you type number 11 in the weekend argument of your NETWORKDAYS.INTL formula or make a string of six 0's and one 1 ("0000001"):
=NETWORKDAYS.INTL(B2, C2, 11, $A$2:$A$5)
Or
=NETWORKDAYS.INTL(B2, C2, "0000001", $A$2:$A$5)
The following screenshot proves that both formulas return absolutely identical results.
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)
As you see, the WORKDAY and WORKDAY.INTL functions make calculating workdays in Excel quick and easy. Of course, your real-life formulas are likely to be more sophisticated, but knowing the basics helps immensely, because you can remember only a small set of essential things and derive the rest. I thank you for reading and hope to see on our blog next week!
170 comments
I'm am trying to figure out a formula to track employee travel days in the last 365 days and the last 730 days. How would I word the argument so as to remove days that fall outside of either the 365 or 730 day period?
Hi!
To select values by condition, try the FILTER function.
I need to return the Wednesday within the same week, what function should I use.
For example, within week 52 - start date is from 26/12/2022 to 31/12/2022, I need to return the result the 4th day of the same week, which is 28/12/2022.
Date 4thday of same week
1. 26/12/2022 28/12/2022
2. 21/12/2022 28/12/2022
3. 25/12/2022 28/12/2022
4.28/12/2022 28/12/2022
Hi!
Use the WEEKDAY function to determine the day of the week.
If I understand your task correctly, try the following formula:
=A1+3-WEEKDAY(A1,2)
I am trying to create a start date based on the end date and how many days it will take to do a job using WORKDAYS.
Cell A = Start Date
Cell B = End Date
Cell C = Days to Complete
If I have an end date of September 30 (Cell B) and it takes 19 days to complete (Cell C), and we don't work weekends, how can I get a start date that is before the end date? Currently, it is adding the days, and I need it to subtract.
I figured it out, just had to put minus sign in front of Cell C
=WORKDAY(B8,-C8)
Hi!
To subtract the number of working days from the current date, use the WORKDAY.INTL function. In the days argument, use a negative value. This is described in the article above. Read carefully.
I want to subtract 4 days from the date in a cell. if the resulting date falls on a weekend, I want it to return the first weekday before that date. I don't want it to calculate 4 days, and then add in the weekend to the calculation. I tried =workday($I$4,-4) which went over a weekend. $I$4 is 8/8/22, so the result was 8/2/22 because the span went over a weekend.
Hi!
If I understand your task correctly, the following formula should work for you:
=IF(WEEKDAY(A1-4,2)>5,A1-4-(WEEKDAY(A1-4,2)-5),A1-4)
Use the WEEKDAY function to determine the day of the week.
I have a problem. here in the Philippines, we have regular holidays, special non-working holidays, and special working days, .. how do I insert that in the automated attendance sheet? so i can set a conditional formatting for specific holidays.. I start with =IFERROR(IF(C6="",1,MATCH(C6,RegularHoliday,0)),0).. but it will only include regular holidays.. any formula that will set all three kinds of holidays?
Hello!
If you need to search in several ranges, I recommend to pay attention to this article: VLOOKUP with IF statement in Excel. If this is not what you need, describe in more detail what result you wanted to get.
Hello, Can I add time stamp to the below formula along with the date, for example received date+time + 2 working days and the output should be start date and time + 2 working days at the same time.
To add 30 workdays to the start date, Friday and Saturday counted as weekends and holidays in A5:A8 excluded:=WORKDAY.INTL(A2, 30, 7, A5:A8)
Hello!
You can add time and days to the date. To add 2 working days, use the formula:
=WORKDAY.INTL(A2, 2, 7, A5:A8)
To add time I recommend reading this guide: How to calculate time in Excel - time difference, adding / subtracting times.
I hope it’ll be helpful.
I have the following in a table with the # days calculated as networking days how do I calculate the % complete?
L M N O
Row 11 Start Date Days End Date % Done
Row 12 Jun 18, 2022 4 Jun 23, 2022 ???????
Hi!
I am not sure I fully understand what you mean. Explain the problem in more detail and write the expected result.
I have the following in a table
Start Date # Days End Date % Done
Jun 18, 2022 4 Jun 23, 2022
Hello, I have an odd request. I'm a RE Broker and I'm trying to have a spreadsheet that will calculate the date that the Earnest Money and Option Money need to be received by, which is 3 days. They must be receipted on a weekday, however, you can count the weekends as a day. HOWEVER, you cannot receipt the funds on a holiday or on a weekend. So if day 3 is a weekend or a holiday, it must be the next non-holiday weekday. Is there a way to figure this out?
Hello!
Add 2 days to the date. Then, using WORKDAY.INTL function, add 1 day, including weekends and holidays.
=WORKDAY.INTL(A1+2,1,"0000011",F1:F10)
F1:F10 -list of holidays.
Hope this is what you need.
Hello. I an trying to find a formula for the following:
U2 is the start date
V2 is the end date, but may be blank
If V2 is blank, count NETWORKDAYS U2 to TODAY, otherwise if V2 is not blank, count NETWORKDAYS U2 to V2
Hello!
To meet your conditions use the IF function:
=IF(V2 < > "",NETWORKDAYS(U2,V2),NETWORKDAYS(U2,TODAY()))
I read it all, and I have some Corrections / Improvements suggestion:
1.
(In 'Opening Paragraph')
Correct "as well as coming Excel 2016" - It had already come many years ago!
2.
(In "Excel WORKDAY.INTL function" section)
2a.
I don't think "Weekend string" should be highlighted in bold - it is not a name of an argument.
I suggest highlighting in italic or underline.
2b.
Add (useless) Edge Cases notions:
"0000000" - is legal "Weekend string" and acceptable
"1111111" - is illegal "Weekend string" and unacceptable; Unless, the "Days" parameter is 0
2c.
(also add this info to '#VALUE! error' list, in the following section ("Excel WORKDAY and WORKDAY.INTL errors"))
3.
(In "Excel WORKDAY.INTL function" section)
You had pasted the wrong sheet table picture - the same as of "Excel WORKDAY function"'s section, just above it ("excel-workday-function.png").
Please update with the correct picture.
Other than that,
Thanks, once more, for all your efforts and detailed illustrated content!
How to subtract working days using NETWORKDAYS.INTL?
For example:
Getting the incurred days as of today from Target Date excluding Fridays and Saturdays.
Thanks in advance.
Hi,
Pay attention to the following paragraph of the article above — NETWORKDAYS.INTL - count weekdays between 2 dates with custom weekends
Hi,
I have a list with
A1 : Employee ID ==== XXXX
B1 : Shift ==== S1
C1 : File Allotted Time Stamp ==== 1/7/2021 8:45 AM
D1 : File Working Hours ==== 3Hrs
E1 : Daily Shift Start ==== 10:00 AM
F1 : Break1 Start ==== 11:15 AM
G1 : Break1 End ==== 11:30 AM
H1 : Lunch Start ==== 1:00 PM
I1 : Lunch End ==== 1:30 PM
J : Break2 Start ==== 3:00 PM
K : Break2 End ==== 3:15 PM
L : Daily Shift End ==== 6:00 PM
M : Dead Line (Using Formula) ==== ????
I need the output for deadline is 1/7/2021 10:30 AM
Example 1
Start date 16 NOV 2020
End date 16 NOV 2021
Diff in months is 13 months , i.e. 395 days
Example 2
Start Date 01 Dec 2020
End Date 31 Dec 2021
Diff is month is 12 months, i.e. 395 days
why datedif is giving this error as there are 13 month in example1 it gives 395 days and in example2,
12 months and also gives 395 days, how can the days be same?
I know i am somewhere wrong but not able to trace, any help is highly appreciated.
Hi,
Please specify what formula you used and what problem or error occurred.
Hi, I am trying to get a due date for each calendar day from 30 days back not counting weekends and holidays. example: January 2, 2021 would have a date of Nov 20,2020. I am confused with the workday.intl. Would i put in column A my start date and then column B my holidays and then what for the formula? (i put..this but came up with an error =workday.intl(A2,-30,0000011,A5:A8) What am i doing wrong?
Help i am a beginner at this.
Hello!
Pay attention to the following paragraph of the article above - Excel WORKDAY function - add or subtract workdays.
=WORKDAY(A1,-30,E1:E6)
where E1:E6 - holidays.
I hope my advice will help you solve your task.
Hello ☺
I have a different scennario. I have monthly working schedule for many employees, i need to run a formula or highlight cells when the working days for one emplyee exceed 6 days in a row apart from the weekends becuase emplyees can work in weekends.
Thank you in advance .
Hello!
Sorry, it's not quite clear what you are trying to achieve. Weekend work is not counted? But between weekends 5 days. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Pervious date:
current Date:
Next working date:
Saturday is working day.
please guide how to write formula accordingly.
Hello!
I hope you have studied the recommendations in the above tutorial.
Use WORKDAY.INTL function
Hi, at my PC in windows 7, excel sheet NETWORKDAYS is giving incorrect answers. Actually i want to manage my workdays attendance and i tried it. In the company i work with, we follow 14-by-14 duty roster therefore I put 2-April-2020 in start date cell and 15-April-2020 in end date cell, the result should be 14 but it is giving 10-January-1900. I tried many times using different methods, took also google help but all in vain and time wasting. Your help would be very helpful for me. Thanks.. Regards.. Roy Muhammad Khan
Hello All,
I am trying to highlight dates using 3 conditions 10, 20 and 30 days in future using Conditional Formatting.
I have used this formula for 10 days which is not working.
What am I missing. Any help please?
My objective is to highlight 10, 20 and 30 days excluding Fri and Sat.
=AND($B2>TODAY(), $B2<=WORKDAY(TODAY(),10,WEEKDAY($B2,1)<5))
Thanks in advance.
Hello All,
I am trying to write a formula including IF, ISBLANK and Workday.
To fulfill these conditions:
• Date holder received Notice
• Date tenure publishing requirements is blank for 15 workdays.
=IF(AND(Date_tenure_Notice>1,ISBLANK(Date_tenure_publishing_requirements>WORKDAY(Date_tenure_Notice,15,Public_Holidays))),"Requires Investigation",IF(AND(Date_tenure_Notice>1,DDate_tenure_publishing_requirements>1),"Investigated",""))
Your help is appreciated.
Regards,
Faryal