How to Calculate Days Between Two Dates in Excel

 Greetings, Excel pupils. Today’s class will make you Excel maestros on calculating days between two dates in Excel. Often basic tasks are trouble creators in spreadsheets and you can't seem to find the right button, formula, or feature to smooth work out. Luckily, that bad luck charm has run out for today and we have some pretty decent formulas lined up to help us out.

Calculating days between dates is not a very foreign concept and may surface as a need for calculating lead time, timelines, deadlines, etc. In this tutorial, we have 3 easy formulas (simple subtraction and the DAYS and DATEFIF functions) for calculating the days between two dates. You will also be briefed on how to use the NETWORKDAYS function to compute weekend days and weekdays and how to include certain holidays in the computations.

Our guide includes practical application of these formulas using a dummy case example. Lead with example they say and lead with example we shall.

Example

Here is the example using which we will test all our formulas. We have 10 products with their order dates in column C and delivery dates in column D.

The aim is to calculate the number of days between the customer placing the order and the product being delivered.

Pro Tip: For working dynamically with the current date, you can use the TODAY function in place of the date in any formula. The argument to use the TODAY function in place of a date is:

The ease of using the TODAY function is that it will be a dynamic argument, updating with the current date and recalculating the formula accordingly.

Now ready to see what’s in store today?

Let’s get calculating!

Method #1 – Subtracting the Dates

Here begin our experiments to calculate days between two dates in Excel. We’ll give the first go to the simplest of all the formulas as it uses no functions; just elementary math. Narrowing that down even further, we will subtract the dates to find the days between them. This is how we’re doing it:

As a rule of thumb of subtraction, the smaller number is deducted from the larger number. This also goes for our dates; the order date in cell C3 is subtracted from the delivery date in D3 which is the later date. This gives us an outcome of ‘5’ i.e. 5 days.

Since the first pair of dates have behaved, let’s apply the formula to the rest of the dates to calculate the days between the two dates:

Method #2 – Using DAYS Function

The next method of calculating days between two dates uses the DAYS function. The DAYS function takes the end date and start date to return the number of days between the two dates. That suits us! Let’s apply the following DAYS function formula to our example:

D3 makes the end date and C3 makes the start date for the DAYS function. DAYS calculates the days between the two dates and returns 5 as the result. No fuss, no frills, we have the results of the DAYS function in our case example:

Method #3 – Using DATEDIF Function

Now we’ll see what the DATEDIF function can do for us while we’re trying to calculate the days between two dates. This elusive function can compute the number of days, months, or years between two dates. Along with the two dates, the DATEDIF function takes one more argument i.e. the unit which defines what the function should return (e.g. “D” for returning the number of days, “M” for returning the number of complete months).

Since it seems to get the job done, what’s so elusive about this function? Being an undocumented function, the DATEDIF function makes no effort to appear in the Excel Formula AutoComplete. If this piques your curiosity, the syntax and the ins and outs of the DATEDIF function can be found here.

Got the idea? Now we can show you how to apply DATEDIF to our case example and calculate the days between two dates:

Following the syntax, we’ve given the DATEFIF function a start date (C3), an end date (D3), and a unit (D) so that DATEDIF calculates the number of days between the two given dates. The formula has been extended to the list of dates and we have the output below:

Calculating the Number of Working Days Between Two Dates

When trying to get the number of days between two dates, you may be calculating from a work point of view. Hence, it will be important to take the weekends into account (or out of account, actually) and deduct them to arrive at the number of working days between two dates.

Thankfully, that’s not something you need to do yourself; we have the NETWORKDAYS functions for that. Now it depends what day(s) the weekend falls for you. That’s still not a problem. The NETWORKDAYS function by default considers Saturday and Sunday as the weekend days. For any other weekend days, we will show you how to use the NETWORKDAYS.INTL function.

Calculating the Number of Working Days (Weekend is Saturday and Sunday)

The NETWORKDAYS function returns the number of whole workdays between two dates. Let’s give this formula a run and see how things turn out:

The two dates given to NETWORKDAYS are C3 and D3. Now, we can end the formula here and we’d have the number of working days computed like below:

But let’s throw another spanner into the works which is an optional feature of NETWORKDAYS itself. The new formula only adds the third argument and will be:

If NETWORKDAYS is supplied with a range of dates as a reference, the function can also exclude them from the count. Therefore, we have a small list of holidays in B15:B17 and we have referred this range in the formula as a third argument. The range is locked into an absolute reference in the formula with $ signs (to apply, position the blinking cursor before, after, or in the middle of the cell reference and press the F4 key).

Now the results will change slightly, the NETWORKDAYS function having to exclude holidays from the calculation of days between the dates:

Notice the change? The number of working days in the instances where the holidays are falling, is a day less than before the holidays were included.

Calculating the Number of Working Days (Weekend is not Saturday and Sunday)

Your weekend may not fall on Saturday and Sunday or you may have a single-day weekend depending on your job or location. Excel has understanding of these (and sympathizes with the latter) and therefore has the NETWORKDAYS.INTL function for accommodating weekends falling on different days.

The NETWORKDAYS.INTL function requires one more argument than the NETWORKDAYS function and that is the weekend parameter. NETWORKDAYS.INTL will return the number of full workdays between two dates according to the selected weekend parameter. Let’s have the formula:

The formula is a copy of the one used for NETWORKDAYS except for the added argument for the weekend days. The NETWORKDAYS.INTL function will calculate the days between C3 and D3, taking into account e.g. Sunday as the weekend days and the given holidays in B15:B17. When typing the formula, the Formula AutoComplete will display the parameters for the selection of the weekend days:

The formula returns these results with our case example:

Recommended Reading: How to calculate business days between two dates

Calculating the Number of Weekends (Weekend is Saturday and Sunday)

We just saw how to calculate the number of weekdays and now we will calculate the number of weekend days between two dates. This will require the NETWORKDAYS function with the DAYS function.

The concept is to calculate the total number of days between two dates using the DAYS function and deduct the number of working days from it, computed using the NETWORKDAYS function. By now we know how both functions work so let’s move onto the formula:

The DAYS function starts off by calculating the number of days between the dates in C3 and D3. This part of the formula returns 5 as the total days. We are adding 1 day here as the DAYS function calculates the difference between the two dates without including a full day. 5+1 brings us to 6 days.

In the next part of the formula, we calculate the working days between C3 and D3 using the NETWORKDAYS function. This gives us 4 working days. The result of NETWORKDAYS is deducted from that of DAYS i.e. 6-4 and we arrive at the weekend days as 2 days.

Our case example with this formula looks like:

Calculating the Number of Weekends (Weekend is not Saturday and Sunday)

And again, if your weekend falls on a different day, the NETWORKDAYS.INTL function can be used interchangeably in this formula. The new formula would be:

The NETWORKDAYS function has been swapped for the NETWORKDAYS.INTL function and the weekend parameter has been added, making Sundays the only weekend day. This is how the results change using the above formula:

Class dismissed! That’s what we had on easy ways to calculate days between two dates in Excel. We also covered excluding holidays from the count and how to calculate working days and weekend days between the dates. We’d like to see you back for the next lesson. Topic hint: Excel matter!