Excel Formula to Calculate Number of Days Between Two Dates

In this article we will demonstrate several methods to calculate the number of days between two dates using a simple formula.


Download Practice Workbook


Find Number of Days Between Two Dates in Excel: 8 Examples

We used the Microsoft 365 version here, however you may use any other version available to you. Please leave a comment if any part of this article does not work in your version.

Method 1 – Using the Subtraction Method

Suppose we have a dataset containing some projects with their launch dates & closing dates. Let’s find the differences (number of days) between those two sets of dates.

  • In cell E5 enter the following formula:
=D5-C5

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

  • Press Enter.

The number of days between the Launch Date & the Closing Date for the first project is returned.

Subtraction Method to Find Number of Days Between Two Dates

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

Dragging Fill Handle in Excel

The total days for all projects are filled at once.

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


Method 2 – Using the DATEDIF Function

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

Steps:

  • In cell E5 enter 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 and use the Fill Handle to get all calculated results up to cell E9.

Using Fill Handle icon to copy and paste formula in Excel

Calculating the Number of Months Between Two Dates in Excel:

To calculate the months instead of days between two dates, use “M” instead of “D” in the DATEDIF function:

=DATEDIF(C5,D5,"M")

Method 3 – Using the DAYS Function

Another simple method to use here is the DAYS function.

Steps:

  • In cell E5 enter the following formula:
=DAYS(D5,C5)
We only have to input the Start Date (D5) &End Date (E5) as arguments in the function.

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

  • Press Enter and use the Fill Handle to return the results in the rest of the column.


Method 4 – Using the DAYS Formula

We can also add two dates directly inside the DAYS function to get the number of days between them.

  • Enter the following formula in cell E5 to get the days between the dates 17th April 2021 and 3rd March 2020:
=DAYS("4/17/2021","3/3/2020")

Note:

Use MM/DD/YYYY format or Excel may interpret the dates erratically.

Finding Number of Days with Text Dates Inside the DAYS Formula

This method is not so effective for a large range of data as it’ll take time to type manually every time.

Read More:


Method 5 – Using DATE Function to Input Dates Directly in the Function Bar

This is a similar method requiring manual input of dates, but using the DATE function.

Inside the arguments, the date format will be (YYYY,MM,DD).

  • Enter the following formula in cell E5:
=DATE(2021,4,17)-DATE(2020,3,3)

Using DATE Function to Input Dates Directly in the Function Bar

Read More: Excel Formula to Count Days from Date


Method 6 – Calculate Number of Days Between Today and Another Date

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

Steps:

  • Select cell E5 and enter the following formula:
=C5-TODAY()
  • 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

To find the number of days remaining from today until a project’s deadline, subtract today’s date from the deadline date.

  • Enter the following formula in cell D15:
=TODAY()-C15

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


Method 7 – Using the NETWORKDAYS Function to Include Weekends & Holidays

The NETWORKDAYS function also counts the number of days between two dates including weekends and customized holidays. Returning to our dataset, let’s calculate the total working days including weekends & a list of holidays mentioned at the bottom.

Steps:

  • Create two empty columns adjacent to our dataset to store the total working days excluding and including holidays and weekends.
  • Add all theholidays in a separate table as in the image below.

Dataset to use NETWORKDAYS Function to Include Weekends & Holidays

  • In cell F5, enter the following formula & press Enter:
=NETWORKDAYS(C5,D5,$C$11:$F$12)

  • In cell F5, enter the following formula and 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 and D5 are Start Date & End Date.
  • $C$11:$F$12 means we’re selecting the holidays from the array as absolute cell references, as we’re going to use that exact range in our formulas in all the cells. To learn more about Absolute Cell References visit this article.
  • The NETWORKDAYS function returns only the weekdays excluding all holidays and weekends between two dates. Using this function, you can’t choose weekends. They’re 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 the Fill Handle icon.

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


Method 8 – Using the NETWORKDAYS.INT Function to Include Customized Weekends & Holidays

We can also select weekends customized to our requirements using the NETWORKDAYS.INT function.

Steps:

  • Prepare the dataset and list the holidays as in the image below.

  • In cell F5, enter the following formula & press Enter:

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

Formula Breakdown:

  • C5 is the Start Date or Launch Date
  • D5 is the End Date or Closing Date
  • $C$11:$F$12 is the list of holidays.
  • ‘7’ has been chosen from the list which contains Friday & Saturday as weekend days. There are other options.

List oh holiday items in NETWORKDAYS.INT Function

  • Autofill the other cells using the Fill Handle.

NETWORKDAYS.INT Function to Include Customized Weekends & Holidays

We have determined the number of working days including customized holidays and fixed weekends (Friday & Saturday).

Read More: How to Calculate Working Capital Days in Excel


Excel Calculator for Number of Days Between Two Dates

Use the free, downloadable Excel file at the top of this article as a calculator. 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


Knowledge Hub


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

Get FREE Advanced Excel Exercises with Solutions!
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

4 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.

  2. Hello, this was helpful. I am wondering if you know how I can do a countdown from a certain date and then use the TODAY feature, while also excluding holidays and weekends?

    • Hello Rebecca,

      Thanks for your compliments! Your appreciation means a lot to us.

      You can create a countdown from a specific date, excluding weekends and holidays, using a combination of the TODAY, ABS and NETWORKDAYS.INTL functions in Excel.

      Excel Formula: =ABS(NETWORKDAYS.INTL($C5,TODAY(),1,$C$11:$D$12))

      Comment-reply-of-countdown

      Hopefully, you have found the solution you were looking for. You can download the workbook used to solve your problem from the following link:
      CountDown Excel File.xlsx

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo