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:
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).
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:
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.
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:
This will also return the number of days between B3 and B4.
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:
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.
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.
1 or omitted
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.
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.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.