I can still remember when I learned about the WORKDAY and NETWORKDAYS functions. I found them very useful and used them regularly but since I also do a lot of project calculating in Excel, the rigidness of the weekends and holiday exceptions only were not working for me. On many occasions I added the exceptions that were not holidays but had to be excluded from working days to the holiday list.
But If someone was working every day but Monday, Wednesday and Friday (so including Saturday and Sunday as working days), that was quite a lot of exceptions on the holiday list, and also quite a few holiday lists by that matter. But by default, the weekends were non working and there was nothing you could do about that.
It was months later that I found the WORKDAY.INTL and NETWORKDAYS.INTL functions and they knocked my socks off. It was exactly what I needed and it fixed all I thought was “wrong” with the WORKDAY and NETWORKDAYS functions. Just the other week I was using it again and thought I wonder if everybody knows this. Since the answer is “probably not”, I decided to write about it on Excel Unplugged.
The workday function needs a Start Date and the number of days in the Days argument. What it calculates is the End Date that is the number of Days from the Start Date skipping all weekends. If you observe the WORKDAY Function Wizard, you can see that there is a third argument called Holidays but it is not bold formatted. That means that it is not mandatory but it is an added functionality. If you add them, then those dates are also counted as non-working and skipped by the WORKDAY function. For example if a test was started on the 13^{th} of April 2015 and needs 360 days of work, it will be completed on the 29^{th} of August 2016 if you disregard holidays. Moreover, it will finnish on the 14^{th} of September 2016 if you consider the holidays.
In many ways, the NETWORKDAYS Excel function is opposite to the WORKDAY Excel function. Opposite in a sense that the NETWORKDAYS function requires the Start Date and the End Date parameters and returns how many working days passed between the two.
Just like with the WORKDAY function, the NETWORKDAYS function excepts the optional argument of holidays. So a list of days that are not working days.
Now imagine that we need to calculate more or less the same thing but also adding every Tuesday and Thursday to exceptions. IF we wanted to do that with the WORKDAY and NETWORKDAYS functions, we would have to add all dates occuring on Tuesdays and Thursdays to the holidays column in the table and to the third argument of the WORKDAY and NETWORKDAYS functions.
But there is a much easier way. The WORKDAY.INTL and NETWORKDAYS.INTL functions.
WORKDAY.INTL is basically the same as WORKDAY but with one crucial distinction. The Weekend argument. The idea is that Workday International function lets you choose which days in a week are the weekend. Sounds funny but that’s what it’s intended to do.
But the Weekend argument hides a secret gem. You can actually do two things with it. First, you can write any of the numbers in the table below and the days in the second column will be used for Weekends.
Weekend argument |
Which Days |
1 or left blank |
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 |
But this gets even better, you can write a string of seven zeroes and ones. Beware it must be a string, so it has to be written like this
"1010100"
With this you tell the WORKDAY.INTL function which days of the week are working and which are free. If you look at the sample in the picture bellow, the formula is
=WORKDAY.INTL(C2,C4,"1010100",holidays[Holidays])
And the Weekend argument says that only Tuesday, Thursday, Saturday and Sunday are working days. So in one move, you tell the Workday function that all Mondays, Wednesdays and Fridays are exceptions. Pure Brilliance!
For instance, if you had the Start Date and End Date in Excel, then you could calculate the number of workdays between them with the NETWORKDAYS function which also has an “international version” called NETWORKDAYS.INTL. IF you look at the picture bellow, you will see that NETWORKDAYS.INTL also has a Weekend argument which is just the same ad Weekend argument of WORKDAY.INTL described above.
And the NETWORKDAYS.INTL function in Excel is very similar. It differs from the NETWORKDAYS function in one argument – Weekend. And all the brilliance described above can also be used when filling out the Weekend parameter of the NETWORKDAYS.INTL function.
You can find all the ins and outs of the NETWORKDAYS.INTL function here.
The Workbook I used for this sample can be downloaded here.
Welcome back to Artificial Intelligence in Excel! In Part 1, ...
Highlighting Weekends can be hard in Excel. So just for fun, ...
The Excel SEQUENCE function In this article, we’ll ...
Last year, Microsoft announced the introduction of a new group of ...
This site uses Akismet to reduce spam. Learn how your comment data is processed.
This is really a great tips!! And I believe many users are not aware of it. I just learned it today 🙂
Just a reminder to the text string where 1 represents non-working day; 0 represents working day. To exclude Tue and Thu, I think the text string should be “0101000” instead. Right?
Correction: “0101000” to “0101011”
You are right! Thanks, I’ve gone and corrected it.
If I created a formula to create the weekend function for specific days worked and the result is posted in cell A2 as: 0001010 (changeable)
and i want the result from A2 to be automatically enter in cell B2, where there is a Workday.INTL formula in the weekend section, how do I incorporate that?
So far it seems like I have to change the formula manually in cell B2 because when [weekend] is entered in the formula as =WORKDAY.INL(start date, days, A2) it generates an error: #NUM.
@Jamal,
you can solve this two ways, you either insert the 0001010 preceded by an apostrophe (‘) or you change your formula =WORKDAY.INTL(start date,days,TEXT(A2,”0000000″))
Both will work.
… But what if the first working day can be saturday or sunday? And when on leave saturday and sunday does not count in a number of annual leave days…
How to do that whit workday function? Pls
IT’s all there, you just have to put it together. This is exactly the case that the Workday.intl function is made for. If you noticed the string “0000000” has the length of seven, so you can make any day a working day and any day a non-working day. But for the annual leave, you use the holidays section.
Hope this explains things.