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!
172 comments
4. Expand Weeks
a. In columns A through D you have a list of weeks with the corresponding start and end date for each week as well as the number of business days in that week.
b. Please expand this data such that all the days are represented vertically along with the corresponding weeks
c. You can see what the output of your work should look like as an example on the right
d. Again, this should all be constructed formulaically such that as the weeks change, your expanded data adjusts dynamically with the new data.
What formula could I use to do this?
Hi! In this blog, we don't help you do teaching tasks for you, but answer specific questions about formulas and working in Excel. Describe the problem in detail, give an example of source data and the desired result.
I recommend reading this guide: Excel WEEKNUM function - convert week number to date and vice versa.
There is not enough data in your question to give you more precise advice.
Hi,
I have been using the networkdays.intl to find the difference between two dates, excluding the weekends and holidays of the country but how can i also add the staff personal vacation dates to calculate the worked days between the start and the end date for a staff list. The vacation dates varies for the staff. For example, the start date was 01/08/2024 and the end date was 25/08/2024, weekends were Saturday and Sunday, I have the defined the holidays of the country in a column which is applicable to all staff. how do i apply vacation days to calculate the worked days?
Hello Jolly!
Try calculating the 2 differences in days and summing them up. C1 and D1 are the beginning and end of the vacation. For example:
=NETWORKDAYS.INTL(A1,C1,1,K1:K10) + NETWORKDAYS.INTL(D1,B1,1,K1:K10)
hi any one here can help me find a formula on submission of weekly report for example august 12-18 should pass or send on august 21
Hi! Unfortunately, this information is not enough to understand what you need. I'll take a guess and maybe you'll find this article useful: Create a date sequence in Excel and auto fill date series.
Hi there!
I need to calculate how many days have been completed in a month (excluding the days we're closed) so that we can see if we're over/under the goal for the month. This number needs to change daily on a screen for sales people. We have a field showing "Days Completed in Month so far" and "Days in Month." I can't figure out how to do the first one accurately. Thanks!
Hello Amy!
Using NETWORKDAYS formula, you can count the number of working days between the first day of the month and the current date, which you can get using TODAY function.
Hi Alex,
You have a wonderful website. The content and explanations are better than Microsoft's instructions on Excel. I have learned a lot from your website.
I have a suggestion: maybe allow upload of screenshots in the comments sections, to allow you to understand what a person is trying to do.
I have a question as follows.
I would like to create a spreadsheet for staff to enter comments and issues during their shift, and each sheet is for the one particular day. All sheet have the same format. How can I automatically insert day of the week and date in particular cells for every sheet, instead of manually doing it for every sheet?
In other words: Sheet 1 is named 1st Jan. Cell B1 automatically puts in day of the week for 1st of January. Cell E2 automatically puts in date in format of 1/01/2024. Sheet 2 is named 2nd Jan. Again, Cell B1 automatically puts in day of the week for 2nd of January. Cell E2 automatically puts in date in format of 2/01/2024. And so forth for every day/date of the month.
Thank you in advance.
Hi Ethan!
You can use VBA to insert a date in a cell that depends on the name of the sheet.
Thank you
I am trying to complete a lab tracker for my patients. They all have labs every 3 months at the least. So, I am looking for a formula that would calculate when a person's next lab is due. For example, John has labs every 6 months. I want to count 6 months ahead and calculate what day his next lab is due. I want ALL days counted including weekends and holidays. Also how to do the same formula for days, like every 28 days.
Hello Debra!
The answer to your question can be found in this article: How to add and subtract dates, days, weeks, months and years in Excel. For example:
=DATE(YEAR(A1), MONTH(A1) + 6, DAY(A1))
If you want to create a sequence of dates with an interval of 28 days, use these guidelines: Create a date sequence in Excel and auto fill date series. Based on the information provided, the formula could look like this:
=A1+SEQUENCE(5,1,A1,28)
I'm looking for a formula where i can calculate hours difference with two date and time, assuming total work days is 60 days and work hours is 8 hour per day.
Hi! For an example of how you can calculate the number of hours worked between two dates, please click here.
Hi,
I'm looking to create a list of dates in a specific year that lists - for example every third Thursday and if one particular Thursday is a holiday it should mark it as red
Is this possible using the above mentioned formulas. I have been trying but I get stuck on errors.
This is to easily set meeting days for recurring meetings for my non profit organisation
Thanks,
Dominicus
If I understand what you're asking correctly, try to enter the following formula in cell A1 and then copy it down along the column:
=SMALL(FILTER(SEQUENCE(52,1,DATE(2024,1,4),7), (MONTH(SEQUENCE(52,1,DATE(2024,1,4),7))=ROW(A1))),3)
The DATE function sets the date of the first Thursday of the year. The SMALL function extracts the third highest date in each month. The Thursday dates in each individual month are got using the FILTER function and the MONTH function.
Thanks,
Looks so easy when you see it, but could never have figured it out myself
Sincerely,
Dominicus
Hi, I am wanting to create a formula for the below scenario:
We do payments every 5th day of the month (5th, 10th, 15th, 20th. 25th and 30th) but I am wanting if the date falls on a weekend to go forward/back to the weekday i.e.. if the 20th is a Sunday to go to either the 18th (Friday) or 21st (Monday).
Thanks
Hi! Determine the day of the week using the WEEKDAY function and use it as a condition in the IF function.
Read more: Excel WEEKDAY function: get day of week, weekends and workdays.
For example:
=IF(WEEKDAY(A1,2)>5,WORKDAY(A1,1),A1)
=IF(WEEKDAY(A1,2)>5,A1+(7-WEEKDAY(A1,2))+1,A1)
Hi, I was playing around with these function and came across a strange thing: let's say start date is 2023-03-01 (B1) and end date is three years later 2026-02-28 (B2) the result of Networkdays(B1;B2) is 783. Now when I use Workday(B1;783) I get 2026-03-02 which is two days later. How come?
Hi! These functions count dates differently. For example, the NETWORKDAY function counts the start date, while the WORKDAY function counts from the next day.
I am looking for a formula where I can calculate the working days base only on start date.
Hi! To calculate the number of working days, you need a start date and an end date. Detailed instructions and examples are available in the article above.
I want to count working hours only between two different dates but I have weird condition as I want to count "9 hours" as per day between Monday to Thursday and 4hours every Friday.
Saturday and Sunday are non working days + calendar holidays. I'm super stressed and I did not find solution even I watch all youtube and google so much.. can someone rescue me please? If anybody need excel sheet I can send it over. Thanks in advance.
Hi! To calculate working days from Monday through Thursday, use the NETWORKDAYS.INTL function and Weekend String "0000111". Calculate the number of working Fridays using Weekend string "1111011". Multiply by the number of working hours.
Please check the formula below, it should work for you:
=NETWORKDAYS.INTL(A1,A2,"0000111")*9+NETWORKDAYS.INTL(A1,A2,"1111011")*4
Hi, I'm trying to create a project management timeline for tasks. I'm using the formula =WORKDAY(F26,E26,US_Bank_Holidays)-1. F26 is the start date and E26 is the number of business days. I'm adding the -1 to the end of the formula because the start date and end date need to be inclusive. For example, a task with a start date of 04-Jan-2023 which takes 2 business days should have an end date of 05-Jan-2023 but without adding -1 excel will return the date 06-Jan-2023. My problem is that the -1 part of the formula subtracts 1 calendar day and not 1 business day (or workday), hence some of my end dates are on Sundays. Does anyone know how I can fix this so that it subtracts 1 workday instead of 1 business day?
Hi! Try subtracting 1 from the "days" argument of the WORKDAY function. For example, WORKDAY(F26,E26-1,US_Bank_Holidays). I hope it’ll be helpful.
Thank you Alexander, that worked. So simple, can't believe I didn't think of it! I'm very grateful!
I am looking to create a formual that will take calculate the amount of day between dates but max each month at 20 days.
Background, I work for a school district and it is considered the teaching year for the purpose of salary is based on on 200 days a year (September 1 to June 30). Each month is worth 20 days (regardless if it less or more). If a teacher is working 0.5 FTE from September 11, 2023 to November 30, 2023 and we count each day of the month up to a max of 20 M-F work days, we want to look at it as 15 days in September, 20 days in October (even though its 22 days) and 20 days in November (even though its 22 days). Take those 55 days, x 0.55 FTE = 30.25 days for that durations. I am stuck on making a formula that will come to this conclusion by capping the months at 20 days. Bonus if you can make it not include anything for months of July and August.
I think your problem cannot be solved with a single formula. Separate the working hours by months and use the NETWORKDAYS function.
Hi all,
I use this formula (=NETWORKDAYS(S2,Q2,$T$2:$T$21)) to calculate how many business days it takes to process a request without the holidays. When I try to increase the holidays, it returns a #value!. Currently, I have from the Jan 1, 2021 to the December 26, 2022 holidays that we have. If I tried to add any other holidays after that, that is when I get the #value!. Am I doing something wrong? All my dates are now in June 2023 so it should pick up the other 2023 holidays, no? Am I using the correct formula?
Thanks for the help!
Hi! I can assume that among your holiday dates there is a cell with text in it.
I currently use a formula to count the amount of days it takes to complete a request. I would like the formula to exclude weekends in the final count of day. Any suggestions?
Current formula which pulls from two different cells with dates inputted in the cells:
=IF(ISBLANK(D1), "", IF(NOT(D1<H1), "1", (H1-D1+1)))
How do I make the formula not count weekends!?
Thanks!
Hi! All the necessary information is in the article above. Use WORKDAY.INTL function.
Hi,
Please I have been trying to see if I can get a formular to calculate days of the week in a month.
E.g
1. To calculate monday to Friday in a month
2. To calculate all the Saturdays in a month
3. To calculate all the Sundays in a month
Thanks
Hi!
To calculate the number of certain week days in a time period, try this formula:
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A$1):INDEX(A:A,A$2)),2)=B1))
where:
A1 - start date
A2 - end date
B1 - day of the week (from 1 to 7), the number of which should be counted
I've been trying to find a formule that when i type out a certain month, the whole date changes from the start month to the end of month. Please reply.
Sorry, it's not quite clear what you are trying to achieve. Perhaps you can use the EOMONTH function to solve your problem. If this is not what you wanted, please describe the problem in more detail.
Hi All, I am using below formula to check how many days are left from certain due date from today. How do i calculate days from a specific due date including weekends using Network days formula. Even after removing the weekend string, its still giving output by weekend exclusion in the days.
=NETWORKDAYS(TODAY(),J3,1)
=NETWORKDAYS.INTL(TODAY(),J6,1)
To calculate the number of days including weekends, use the DATEDIF function. Or use a Weekend String like "0000000" in the NETWORKDAYS.INTL function.
hello all,
i have a question, is the start_date tied down to a specific input date or can it do a search in a date value from a selected range of row?
Example
=WORKDAY(I6:K6,-5); where between range I6:K6, there is only 1 date but it can in that range.
Thank you !
Hello!
The WORKDAY function cannot search for the date you want. You must specify the address of the cell with the date, or find the date using, for example, the INDEX+MATCH functions.
For example,
=WORKDAY(INDEX(I6:K6,,MATCH(TRUE,I6:K6>0,0)),-5)