How to Calculate the Number of Working Days between two dates in Excel | NETWORKDAYS function

In this video, I'm going to show you how to use network days to calculate the number of working days between given start and end dates. So in a previous video, we did it the other way around, where we had the start date and the number of working days required for a given task, and we needed to find the end date. So our answer in that video was a date. In this case, our answer is the number of days between two dates. And that answer is going to exclude weekends, and it can exclude holidays if we wanted to. In this example, we have our project timetable. We have the tasks listed on the left-hand side, and we have specified the start date and the end date. Now, what we need to do is calculate the number of working days, which exclude weekends and holidays. Here, the network days function is perfect for this. The difference between network days and Network Days International is that the first one assumes that weekends fall on Saturdays and Sundays. This is basically embedded in the formula. In the second one, you can define where your weekends fall. So just to demonstrate, let's take the second one. We just need to give it a start date, that's this one, and the end date, and that's in this column. The weekend, I'm going to take one because the project that I'm doing, let's say it's in Austria, and this is the holidays listed in Austria. So if your weekends fall on different days, make sure you select the correct number from this list. For the holidays, that's an optional argument, and I have the list of holidays here, and I do want to exclude them, so I'm going to pick this list and press the F4 key to fix it. That gives me nine working days. Now wait a second, in the previous example, when we did it the other way around, where we had the start date, and we were inputting the number of working days, getting our end date, we got the same end date back as we do now. Our start date is identical, but look at the number of working days that Excel gives back. In this case, it gives us nine, and here we specified eight to come to this day. This is something you have to keep in mind, is that when you use this function, the WORKDAY function, Excel does not take the start date into account, so it starts counting after the start date. But the NETWORKDAY function does take the start date into account, and it starts counting right here. So that's the difference. Now we can test this, I have the calendar right here, so let's say this part, design completed, started on the second, so if I do count this right now, I'm going to count one, two, three, four, that's a holiday, that's a weekend, five, six, seven, eight, nine. Okay, I get 13 back, and that was nine working days. So if I don't want it to include the start date, and let's say in this case I don't, I need to add one to my start date. Now I'm going to highlight this, press F2, Control-Enter. Now let's see if these are identical days to the last one. So the sum of this is 99, the sum of this here is 99. Okay, so don't forget that the NETWORKDAY does calculate your start, it does take your start date into account when it does this calculation, and if you don't want that, you need to add a plus one here. Now since I'm doing this project in, let's say Europe, and my weekends do always fall on Saturdays and Sunday, I can actually use the NETWORKDAYS formula and then just get rid of this additional argument. Now I'm going to take this one step further where we want to create an interactive timetable so that we can select which country we're going to be doing that project in, and depending on the holidays in that country, we're going to get back the number of working days. So now my boss tells me I need to do this project in UK as well. Okay, so I have to make a project time plan based on Austria and one for the UK, and I want to do it in a dynamic way. I want to see what is the number of working days if I did this project in the UK as compared with Austria. I want to have a dropdown here where I can select the country, and this number of working days is going to automatically update based on that selection. First of all, let's go in and add a data validation to this. We're going to select list, and the source is basically these two here. Okay, so let me just put this in the middle. Let's just update the formatting. When I select UK, I need these days to update, and I need them to take the UK holidays into account. So which part of this formula do I need to update? This last part, right? Because the start date and the end date are the same, the part that's variable here is the list of my holidays. So I want this to jump to the UK if the selection here is UK. To get this dynamic selection, index is a perfect formula for this. The first argument of index is the array, and array means the area where your answer is. Our answer is either this or it's this. So basically, I have to select both areas. Next argument is how many rows do I want to move down? Well, I don't want to move down any rows. I want to take every single row into account. So I can skip that argument. The last argument for index, you can see it's in square brackets, it's optional, but it's not optional if you have highlighted more than one column. So if your original array is more than one column, you do need to tell it how many columns to move. In this case, when it's the UK, I need to say go to. If it's Austria, I need to say go one. So a great formula here is the MATCH function because that's going to return the position of Austria and the position of the UK. So that's going to give me the one and the two and return that to the index function. So MATCH requires a lookup value, so that's this one where I'm selecting. Let me just think about the fixing of this. When I highlighted the index area, I do need to fix that with the F4. For my MATCH area, that selection here, this C1 requires a full fixing. Next is the lookup array. So where should it look for this? Should look for it here. So notice that the width of my MATCH area of my lookup array is the same width as my index area. That's important. So now I'm going to fix this as well, and I'm going to return an exact match. Bracket closed, another bracket closed, press Enter. This should give me the number of working days for the UK. I'm going to highlight this. Press F2, Control, Enter. Now we can see we have in total less days if we do the project in the UK than if we do it in Austria. Okay, so that's total of 99 working days versus 97 working days in the UK. So let's just double check one of these. So here for testing, I have nine working days if I do this project in the UK. Now if you go back to the other tab where it was based on Austria, I had 10 working days. So it means that there must be an extra holiday in the UK. So let's check here. That's in April. In April, I have two holidays listed here for Austria. For the UK, I also have two holidays. Then one of these holidays must be, unfortunately, on a weekend in Austria. So let's look at the 16th here. That's a Sunday. The 17th is a Monday. So I just have one day of holiday here. In the UK, I have the 14th and the 17th. So that's a Friday and a Monday. Okay, so that gives me one less working day if I was going to do this project in the UK. So this is how you can use network days in your project timelines or in your reporting timelines. If you have a start date and an end date, you want to calculate the number of working days. You can also then combine this with an index function to take into account different holidays based on the country that you're doing the project in. If you like this video, don't forget to give it a thumbs up. And if you like these type of videos, don't forget to subscribe to my channel so that you can get notifications when new videos like this one come out.