Another formula that calculates the number of days between two dates and excludes certain days from our calculation, is the NETWORKDAYS formula. NETWORKDAYS originally assumed that the days it should exclude are the weekends, so the original NETWORKDAYS formula just requires the startdate and the enddate; it automatically excludes weekends, and it assumes that weekends are Saturdays and Sundays.
Not all countries observe their weekends on Saturday and Sunday (many Middle East countries observe Friday/Saturday as their weekend), and not everyone takes 2-day weekends. Because of this, beginning in Excel 2010 we were provided with an updated version of the formula called NETWORKDAYS.INTL (international). With NETWORKDAYS.INTL we can define the type of weekend that we have in our country as well as a 1-day or 2-day (sequential) weekend.
For our startdate we will use 12/3/2018 and our enddate will use 12/22/2018. For our weekend we can pick Saturday, Sunday or we can scroll down and pick “Friday Only”.
If we wanted to exclude Monday’s only, we would select 12, but in this case, we wish to exclude all Fridays, so we’re going select 16. What this does is counts the number of days between the two dates and while excluding Fridays.
We can use the information from these two formulas to calculate how many Friday’s we have, but there’s one thing we must be careful with and that’s what these days include and exclude.
When we get the difference between these two days, are we including the startdate and the enddate or is one of them, or both, excluded? That is something we must take care of when deducting one from the other.
To check that out, let’s restrict our date range to a smaller range so we can quickly look at the calendar and find out what’s included and excluded.
Let’s switch the startdate to the 3rd (Monday) and the enddate to the 6th (Thursday). There are no Friday included, but look at our results.
We get different answers. The formula in cell B7 states that there are 3 days between the startdate and enddate? One of these days is not included. The formula in cell B7 is excluding the startdate, whereas the NETWORKDAYS.INTL in cell B8 includes both the startdate and the enddate.
To be able to compare them to one another, we need to make an adjustment to the formula in cell B7. We’re going to add a 1 to result. This will ensure we are including the startdate and enddate.