# Calculate the Difference Between Days in Excel

How can you calculate the difference between two dates in Excel?

It sounds like an easy question; subtract one date from the other, right?

Not so fast.

What if you want to exclude the weekends?

What if you don’t work traditional consecutive, two-day weekends? What if your “weekends” are more exotic, like Monday and Thursday?

What if you don’t want to count holidays?

Let’s witness an amazing trick that will allow you to count the number of days between two dates and skip any day combination you wish.

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 + 1

# 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 2 ^{nd}** and

**June 14**.

^{th}=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 2 ^{nd}** and

**June 14**excluding the weekends (

^{th}

*5*^{th}*,*), as well as the holiday dates listed in cell range

**6**,^{th}**12**, &^{th}**13**^{th}**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.

# 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, B2

Here’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.