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:
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.
- 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.
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.
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.
- Press Enter and use the Fill Handle to get all calculated results up to cell E9.
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)
- 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.
This method is not so effective for a large range of data as it’ll take time to type manually every time.
Read More:
- Excel Calculates Difference Between Two Dates in Days
- How to Calculate Number of Months Between Two Dates in Excel
- How to Find Number of Weeks Between Two Dates in Excel
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)
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.
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
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.
- 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.
- After that, Autofill the rest of the cells by using the Fill Handle icon.
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.
- Autofill the other cells using the Fill Handle.
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.
Knowledge Hub
- Calculate Expiration Date with Excel Formula
- Count Months from Date to Today by Using Excel Formula
- Excel Formula to Count Days from Date
- Calculate Years from Today
- Calculate Years Between Two Dates
- Calculate Years and Months between Two Dates in Excel
- Calculate Number of Days Between Two Dates with VBA
<< Go Back to Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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))
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