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 13th of April 2015 and needs 360 days of work, it will be completed on the 29th of August 2016 if you disregard holidays and on the 14th 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.
|1 or left blank||
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
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
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.
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 ...
About two months ago, a team assembled to create an Excel AddIn ...