I can still remember when I learned about the WORKDAY function. I found it very useful and used it 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 function and it knocked my socks off. It was exactly what I needed and it fixed all I thought was “wrong” with the WORKDAY function. 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. This is how it looks on our sample. So 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 and on the 14^{th} of September 2016 if you do.
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 function, we would have to add them to the holidays column in the table and to the third argument of the WORKDAY function.
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!
Just for reference, 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.
The Workbook I used for this sample can be downloaded here.
Last year, Microsoft announced the introduction of a new group of ...
Whatever side of the Cloud vs. On Premise debate you take, ...
We start with a range of values in Excel (A1:G20). Now we are ...
This post is a tribute to the event, that is one of a kind in ...
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.