As usual, we begin with a set of data: a start date, and an end date.
The simplest and most common method of calculating the number of days between two dates is to subtract the latter date from the former date.
=B2 – A2
Because Excel stores dates as whole numbers, it’s very easy to calculate the difference between two dates.
If we add a second set of dates and fill the formula down, we can calculate a different number of days.
BE AWARE…
If we check the dates on a calendar, we see that the first example (blue shade) traverses 13 days while the second example (tan shade) traverses 7 days.
The reason for this is that the first day is not counted. The first day doesn’t occur until a full 24-hours has passed, placing you on the second day.
If you need to include the first day in the result, you can always modify the formula to add 1 to the result.
=B2 – A2 + 1Featured Course
Excel Essentials for the Real World
Excluding Weekends & Holidays
Suppose we want to count only the number of “working” days? We want to exclude the weekends and the holidays if any.
When it comes to letting Excel know your specific holidays (technically, these can be ANY days you wish to skip, not just sanctioned holidays), you need to create a list of the unwanted days somewhere in your workbook.
Below we have created in cells D7 through D9 a shortlist of days we will ignore during the calculations.
Enter the NETWORKDAYS Function
The NETWORKDAYS function returns the number of whole working days between a start_date and end_date. Working days exclude weekends and any dates identified in holidays.
This function has been around for quite some time, which means it has gone through some changes.
Considering not all users observe the same number of days for a weekend, much less the same days of the week, a more robust version of the NETWORKDAYS function was developed. Welcome the NETWORKDAYS.INTL function to the party.
The NETWORKDAYS.INTL function, introduced around Excel 2010, has an extra argument that allows you to define the days those weekends are observed. The original NETWORKDAYS function is hard-coded to observe weekends on Saturday and Sunday.
The syntax for the NETWORKDAYS.INTL function is as follows:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])The NETWORKDAYS.INTL function syntax has the following arguments:
start_date and end_date – Required. These are the dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
[weekend] – Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. [weekend] is a weekend number or string that specifies when weekends occur.
[holidays] – Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.
For the [weekend] argument, below is a table of codes that define the various weekend combinations.
Calculating a Date Difference Without Default Weekends
Let’s start with a simple calculation to determine the number of days between June 2nd and June 14th.
=NETWORKDAYS.INTL(A2, B2)
If we wish to accept the default “Saturday/Sunday” weekend combination, we don’t have to add anything extra to the formula as those days are the function defaults.
NOTE: When using the functions to determine the number of days, the first day and last day (i.e., the [start_date] and [end_date]) are counted in the result.
Skipping Holidays
Now let’s calculate the number of days between June 2nd and June 14th excluding the weekends (5th, 6th, 12th, & 13th), as well as the holiday dates listed in cell range D7 through D9.
As we enter the same formula from above when we reach the third argument [weekends] we are presented with a list of the two-day and single weekend combinations.
We will stick with the default “Saturday/Sunday” combination” and select the number 1 code.
For the fourth argument [holidays], we select the cell range D7:D9 and lock it as an absolute reference so it doesn’t change when we repeat the formula for other examples.
=NETWORKDAYS.INTL(A2, B2, 1, $D$7:$D$9)
Filling the formula down to the next example, we see that 3 days qualify as working days.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
BONUS TRICK – Defining a Custom On/Off Day Pattern
The ability to select from any single or two-day consecutive weekend is built directly into the NETWORKDAYS.INTL function.
But what if your days off schedule are a bit more unconventional? What if your working days are Monday, Tuesday, and Saturday?
NETWORKDAYS.INTL does not possess such a day on/off combination.
The good news is that you can create any combination of days on/off you need with a really slick trick.
Begin by creating a NETWORKDAYS.INTL function with a declared start and end date.
=NETWORKDAYS.INTL(A2, B2Here’s the trick: for the days on and days off we will create a sequence of zeroes and ones that represent working days and non-working days respectively.
=NETWORKDAYS.INTL(A2, B2, “0011101”)If it helps, think of the zeroes as “eyes open” and the ones as “eyes closed”. The formulas will only include days it can “see” when its eyes are open.
Filling the formula down to the next example, we see that 3 days qualify as working days.
For the Adventurous Among Us
You may be thinking I created the calendar by typing static values in the G4 through M8 range.
The reality is the calendar can be changed to a different month and the days will automatically update themselves.
This was accomplished using the new Dynamic Array function called SEQUENCE available to Office 365 subscribers.
Selecting cell G4 reveals the following single formula that spills through the entire range of calendar cells.
=IF(MONTH(SEQUENCE(6, 7, H2 - WEEKDAY(H2, 3), 1) ) = MONTH(H2), DAY(SEQUENCE(6, 7, H2 - WEEKDAY(H2, 3), 1) ), "")Perhaps it would appear more readable with a few line breaks to separate the main segments.
If you wish to see a version of this calendar produced in a previous post, check out the following link.
Yearly Calendar with a Formula in Excel and Sheets
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
