Here’s an example of how the WORKDAY and WORKDAY.INTL functions work in Excel:
You can download the file here to follow along.
We have a project that we want to start on 1/2/2014. We know it’s going to take 210 days to complete and there are 3 holidays that we won’t be working. Normally our weekends would be Saturday and Sunday. We want to find out what day we should be done:
There are two functions we could use: WORKDAY and WORKDAY.INTL. The main difference between the two is that WORKDAY only allows for weekends to be Saturday and Sunday, while WORKDAY.INTL has the flexibility to select alternatives for the weekends.
Let’s start building our formula:
For the start date I clicked on cell G4, for the workdays I clicked on G5. Then we are presented with a list of options to choose for our weekends. For this first example, I’ll just pick 1, which is the traditional Saturday and Sunday. Finally, for our holidays, I can just highlight the cells G7:G9. I’ve locked all these as absolute references so that I can copy the formula and make some changes:
The result is that we should be complete by October 27th. Don’t forget to format this cell as a date.
If we decided to change our weekends to only be Sunday, we would change the weekend number from 1 to 11. Here’s the result:
By only having Sunday as our weekend, we can complete the project 49 days earlier.
Try different scenarios and see how it comes out. You can also add other days as holidays, just make sure you increase your range for that list.