Excel Formula to Calculate Number of Days Between Two Dates

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel provides numerous methods to calculate the number of days between two dates using a simple formula. A wide range of functions is available to determine the difference between two fixed dates and here I’m going to show you those useful techniques with proper illustrations & guidance that might fulfill your requirements. Don’t forget to use our Workbook as a free calculator while counting the number of days between any two dates in Excel!


Download Practice Workbook

You can download our workbook here which we’ve used to prepare this article. You can also use this workbook as a calculator while counting the days between two dates. We’ve embedded a number of formulas & functions to help you find your results by inserting or customizing input dates only.


8 Examples with Excel Formula to Get Number of Days Between Two Dates

In this section, I will show you 8 quick and easy examples to calculate the number of days between two dates using a simple formula on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

1. Subtraction Method to Find Number of Days Between Two Dates

Let’s think of a number of projects with their launch dates & closing dates. We need to find the differences or the number of days between those two dates.

  • First, select Cell E5 & type the following formula:
=D5-C5
  • Press Enter & you’ll get the number of days between the Launch Date & the Closing Date for the first project.

Here, we’re simply subtracting the Launch Date from the Closing Date.

Subtraction Method to Find Number of Days Between Two Dates

  • Now point your mouse cursor onto the right bottom corner of Cell E5 & you’ll see a ‘+’ icon there which is known as Fill Handle.
  • Click on this icon, drag it to Cell E9 & then release the mouse button.

Dragging Fill Handle in Excel

  • And you’ll see the total days for all projects at once.
  • You can use each of the worksheets as a calculator, just change the start & end dates and find the number of days between them within seconds.

Calculation of Number of Days Between Two Dates in Excel


2. Using DATEDIF Function to Get the Number of Total Days Between Two Days

We can use the DATEDIF function too to obtain the same results.

📌 Steps:

  • First, select Cell E5 & type the following formula:
=DATEDIF(C5,D5,"D")

🔎 Formula Breakdown:

In the formula-

  • C5 is the Start Date or Launch Date
  • D5 is the End Date or Closing Date
  • “D” denotes the parameter- Days; as we’re going to find the number of days between two dates here.

Excel Formula using DATEDIF function for Number of Days Between Two Dates

  • Press Enter & use Fill Handle like before to get all calculated results up to Cell E9.

Using Fill Handle icon to copy and paste formula in Excel

Calculate the Number of Months in Two Dates in Excel:

To calculate the months between two dates, you have to use “M” instead of “D” in the DATEDIF function.

=DATEDIF(C5,D5,"M")

3. Inserting DAYS Function to Calculate Number of Days Between Two Dates in Excel

Another simple method here is to use the DAYS function.

📌 Steps:

  • In Cell E5 & type the following formula:
=DAYS(D5,C5)
You have to input only Start Date & End Date as arguments in the function. Here, D5 is the End Date and C5 is the Start Date.

Inserting DAYS Function to Calculate Number of Days Between Two Dates in Excel

  • Press Enter & use Fill Handle once again to find out other results in the column.


4. Finding Number of Days with Text Dates Inside the DAYS Formula

You can also type two dates directly inside the DAYS function to get the number of days between those two dates mentioned. Insert the following formula in Cell E5 to get days between the dates 17th April 2021 and 3rd March 2020.

=DAYS("4/17/2021","3/3/2020")

Note:

You should insert the date in MM/DD/YYYY format or Excel may interpret the dates erratically.

Finding Number of Days with Text Dates Inside the DAYS Formula

But this method is not so effective for a long range of data as it’ll take time to type manually every time

Read More:


5. Using DATE Function to Input Dates Directly in the Function Bar

And, this is another similar method where you have to input dates manually by using the DATE function.

Inside the arguments, the date format will be (YYYY,MM,DD). To use this way, insert the following formula in cell E5:

=DATE(2021,4,17)-DATE(2020,3,3)

It’s indeed the combination of two methods- Subtraction & DATE functions as we’re subtracting one date from another one by manually inputting dates inside the DATE function arguments.

Using DATE Function to Input Dates Directly in the Function Bar

Read More: Excel Formula to Count Days from Date


6. Excel Formula to Calculate Number of Days Between Today and Another Date

We can use the TODAY function to input today’s date directly & then a past date can be subtracted from it to find the number of days until today from that fixed past date.

📌 Steps:

  • First, select Cell E5 & type the following formula:
=C5-TODAY()
  • Then, drag the Fill Handle icon to apply a similar formula to the other cells of the column.

Excel Formula to Calculate Number of Days Between Today and Another Date

  • Now if we want to know the number of days remaining from today before a project’s deadline then we have to subtract today’s date from the deadline dates. For this, insert the following formula in cell D15:
=TODAY()-C15

Excel Formula to Calculate Number of Days until today from a previous date

  • Thus, you can calculate the day remaining or passed from the date till today.

7. NETWORKDAYS Function to Include Weekends & Holidays

Another formula we can use for counting the number of days between two dates including weekends & customized holidays by inserting the Excel NETWORKDAYS function. Let’s use the previous dataset again. Now we have to calculate the total working days including weekends & a list of holidays mentioned at the bottom. Follow the steps below:

📌 Steps:

  • First, create a dataset and two empty columns to get the total working days excluding and including holidays and weekends. And, insert the all holidays in a separate table.

Dataset to use NETWORKDAYS Function to Include Weekends & Holidays

  • Then, in Cell F5, type the following formula & press Enter to get the days between the two selected dates.
=NETWORKDAYS(C5,D5,$C$11:$F$12)

  • Then, in Cell F5, type the following formula & press Enter to get the days between the two selected dates excluding the weekdays.
=NETWORKDAYS(C5,D5,$C$11:$F$12)

🔎 Formula Explanation:

Inside the arguments,

  • C5 & D5 are Start Date & End Date.
  • $C$11:$F$12 means we’re selecting the holidays from the array (C11: F12) and then locking the Row Numbers & Column Names by using the ‘$’ symbol to make them absolute cell references as we’re going to input those exact holidays every time for all projects. If you need to be enlightened more about Absolute Cell References then you can have a look at this article to get detailed ideas.
  • The NETWORKDAYS function returns only the weekdays excluding all holidays and weekends between two dates. But, using this function, you can’t choose weekends. It is already selected in the formula as Saturday and Sunday.

Using the NETWORKDAYS Function to Include Weekends & Holidays

  • After that, autofill the rest of the cells by using Fill Handle icon.

Calculation of total working days between two dates excluding or including weekends and holidays


8. NETWORKDAYS.INT Function to Include Customized Weekends & Holidays

And now in the last method, we’ll select weekends according to our own choices. For this, we’ll use NETWORKDAYS.INT function here.

📌 Steps:

  • Similarly before, you have to make the dataset ready and make a list of the holidays.

  • Then, in Cell F5, insert the following formula & press Enter:

=NETWORKDAYS.INTL(C5,D5,7,$C$11:$F$12)

🔎 Formula Breakdown:

So, here, in the Formula:

  • C5 is the Start Date or Launch Date
  • D5 is the End Date or Closing Date
  • ‘7’ has been chosen from the list which contains Friday & Saturday as weekend days. You can select even only one weekend too by choosing from the list of weekend options.

List oh holiday items in NETWORKDAYS.INT Function

  • And, $C$11:$F$12 is the list of holidays.
  • After that, Autofill other cells by using Fill Handle.

NETWORKDAYS.INT Function to Include Customized Weekends & Holidays

  • And we’ve just determined the number of working days including customized holidays and fixed weekends(Friday & Saturday) chosen by us.

Read More: How to Calculate Working Capital Days in Excel


Excel Calculator for Number of Days Between Two Dates

There’s a downloadable Excel file at the top of this article. Download it for free and use every worksheet as a calculator. The practice sections are available in all spreadsheets with embedded formulas. You have to simply enter two dates in a proper format and the outputs will be shown instantly!

Practice Section for Number of Days Calculation Between Two Dates


Concluding Words

So, these are all suitable techniques I’ve found for you and I hope, you’ve found these methods effective enough to use anytime. Exercise with our free workbook, and use it as a calculator anytime you need to count the number of days between two dates with an Excel formula. If you got any questions or feedback you can comment here. You can also have a look at our other interesting articles related to Excel functions.


Days Between Dates: Knowledge Hub


<< Go Back to Calculate Dates | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. Great article but I wonder if you know the solution to a slightly more complex issue. Let’s say I want to calculate the days between two dates but I want to include both the starting and ending date in the calculation? If regular days, I can simply +1 the formula but what if I’m using networkdays.intl? I can’t just add a one to it because it depends on whether either the starting or ending day is a “weekend” date, right? How could I work around that??

    • Hello ERIC. Thank you so much for your query. Please follow the formula below to get the days between the two dates, including the Launching and Closing dates.

      =IF(NETWORKDAYS.INTL(C5, D5, 1) > 0, NETWORKDAYS.INTL(C5, D5, 1) + 1, IF(WEEKDAY(C5, 1) < WEEKDAY(D5, 1), NETWORKDAYS.INTL(C5, D5, 1) + 2, NETWORKDAYS.INTL(C5, D5, 1) + 1))

      Here, we used NETWORKDAYS.INTL function along with the IF function. Now, this formula can check if the launching date or the closing date is a weekend and adjust the result accordingly. For a better understanding, you can check Number-of-Days-Between-Two-Dates-Calculator-1 as well.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo