This lesson shows you how to use Excel to calculate the number of days between two dates. This might seem like a simple task, and it often is. However, in this lesson we will look at four different scenarios:

1. Calculate how many days there are between two dates, using simple calculations as well as the DATEDIF function.
2. Calculate the number of weekdays (Mon-Fri) between two dates using the NETWORKDAYS function.
3. Calculate the number of working days between two dates excluding vacation or holiday dates, also using the NETWORKDAYS function.
4. Calculate the number of business days between two dates where the weekdays may be different to Monday-Friday, using the NETWORKDAYS.INTL function.

However, we will start by looking at how Excel stores dates; this is important to understanding how Excel calculates dates in the different scenarios above.

Excel stores dates as numbers

When you type a date into a cell in Excel, the date is actually stored as a number. This makes it possible for you to perform calculations using dates, such as subtracting one date from another to find the number of days between them.

Here are some examples of dates and the number that Excel is storing for those dates:

As you'll see, this list starts at the first of January, 1900, which Excel stores as 1. From there, the dates become more recent, and the numbers stored increase.

Note also how the second of January, 1900 is stored as 2. So that means that each positive whole number refers to a specific day and date. It also means you can also perform calculations on dates as if they were numbers - because they are numbers.

There are a couple of additional things to be aware of with how Excel stores dates. These may affect how you use dates in Excel:

• Excel for Windows uses something referred to as the 1900 date system. Older versions of Excel for Mac use the 1904 date system (the number 1 represents the First of January, 1904). This is not normally an issue when transferring files between Windows and Mac computers. However, if you do have problems, you can change the settings in Excel for Mac to use the 1900 date format (or change the settings in Excel for Windows to use the 1904 date format).
• Excel can't handle dates earlier than the first of January, 1900. There are ways to work around this (as outlined in this article) but, in general, you need to remember that you can't use Excel's date system for dates earlier than 1900 (or 1904 on a Mac).
• Dates are normally expressed as whole numbers. However, it is possible to use decimal numbers to specify a specific date and time. For example, 10000.25 would be 6am on February 19, 1982 (since .25 = 25% of one day, or 6 hours).

Note that it is possible for a cell in Excel to contain text that looks like a dates. If this happens, your calculation or formula will return a #VALUE error. If this happens, check out our lesson on converting text values to dates (opens in a new tab).

Calculate how many days there are between two dates

Now that we know that Excel stores dates as whole numbers, it is easy to use Excel to calculate the number of days between two dates - you can simply subtract one date from the other, as shown in the example below:

The formula is simple - subtract the end_date from the start_date:

• =B4-B3

This example shows the same dates using two different formats. The point here is that, as long as the cells contain valid dates, the formatting you use does not affect the calculation that Excel performs.

Use the DATEDIF function to calculate the number of days between two dates.

Another option for calculating the number of days between two dates is to use the DATEDIF function. This function was included in Excel to provide compatibility with Lotus 1-2-3 spreadsheets, and is not included in the standard list of formulas in Excel, as you can see in the following example. DATEDIF is a real function in Excel and should have appeared in the list below when we started to type =date into the formula bar.

To use DATEDIF to calculate the number of days between two dates, enter the following formula:

• =DATEDIF(B3,B4,"D")

This will also return the number of days between B3 and B4.

Calculate the number of week days between two dates

If you want to calculate the number of week days (or working days) between two dates, the formula in the example above will not help. In this scenarion we can use the NETWORKDAYS function instead.

The NETWORKDAYS function simply calculates the number of working days between the two dates, and ignores the weekends. The NETWORKDAYS function has this syntax:

• =NETWORKDAYS(start_date,end_date)

Note that the NETWORKDAYS function treats Saturday and Sunday as weekend days. Later, we'll see how to specify different days for the weekend.

Calculate the number of business days between two dates, excluding holidays

You can also calculate the number of business days between two dates excluding holidays that occur during that time.

The NETWORKDAYS function can be extended with an optional parameter that specifies the holidays you want to exclude:

• NETWORKDAYS(start_date, end_date, [holidays])

The [holidays] value is a range of one or more cells that include dates which Excel should treat as holidays and exclude from the calculation.

This version of NETWORKDAYS is used in the example below:

As you can see, the NETWORKDAYS formula now includes a reference to A11:A15, which contains the dates to be treated as holidays:

• =NETWORKDAYS(B3,B4,A11:A15)

Note how the number of days has dropped from 66 to 62, although there are five dates included in our list of holidays. This is because the NETWORKDAYS function automatically excludes weekends from the calculation, so the fifth value on our list (which is a Saturday) has already been excluded.

Calculate the number of week days using non-standard weekend days

What if, for your calculation, Saturday and Sunday are not considered weekend days? For example, you might work shift days which mean that your normal days off are not Saturday and Sunday.

In this case, the NETWORKDAYS won't work for you. Instead, we can use a related function, NETWORKDAYS.INTL, which looks like this:

• NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Note that it is almost the same as NETWORKDAYS, but has a fourth parameter - [weekend]. Note that both [weekend] and [holidays] are optional. If you don't supply either, NETWORKDAYS.INTL functions the same as NETWORKDAYS.

The following example uses NETWORKDAYS with the [weekend] parameter included, but not the [holidays] parameter:

This example uses the [weekend] parameter to specify that Sunday is the only weekend day of the week, for the purposes of the NETWORKDAYS.INTL calculation.

There are 14 different options for the [weekend] parameter that allow you complete control over which day(s) Excel should treat as weekends in the formula. As you can see, 11 corresponds to Sunday only.

Fortunately, you don't have to remember this list if you're using Excel 2010 or Excel 2013 since Excel will automatically show you a list of the options available to you as you type the NETWORKDAYS.INTL function into your formula, as shown below. Unfortunately for Mac users, Excel 2011 for Mac doesn't have this feature.

Lesson Summary

In this lesson we looked at several different ways to calculate the number of days between two dates using simple subtraction, the DATEDIF function, the NETWORKDAYS function, and the NETWORKDAYS.INTL function. If you have any questions about any of the information provided above, or you have a specific scenario that you're trying to solve, please leave a message in the comments below.