Calculate days between two dates in Excel (7 examples)
As an Excel user, you may run into a situation where you need to know how many days are between two dates in Excel. Or you want to count the days between today and a specific date. Or you wish to get the number of working days between two dates. This tutorial will introduce a few easy and quick methods to calculate the number of days between two dates in all three scenarios.
Calculate days between two dates
Using subtraction
Using Kutools to count days, weeks...
Using DAYS function
Using DATEDIF function
Count days between today and a date
Count workdays between two dates
Excluding weekends
Excluding weekends and holidays
Download sample file
Calculate days between two dates
Supposing you have the Start date in cell C2 and the End date in cell C3, you want to find out the number of days between the two dates. In this section, we will show you four ways to achieve your goal.
Using subtraction
To count days between two dates, simply subtract the start date from the end date. Here is the generic formula:
Step 1: Input the subtraction formula
In cell C6, apply the following formula, then press the Enter button.
=C3-C2
Result
As you can see, there are 180 days between the two dates in cells C3 and C2.
- Excel stores dates as serial numbers that start on 1/1/1900, which is represented by the number 1. So, when you subtract one date from another, you are actually subtracting the numeric values of the dates.
- If the End_date is older than the Start_date, the returned result will be a negative integer. If you want to always get a positive result, you can use the below formula:
=ABS(End_date - Start_date)
Using Kutools to count days, weeks, months, and years between dates
With the Date & Time Helper feature of Kutools for Excel, you can also calculate days between dates. What’s more, this feature is more powerful because it offers more options, like calculating weeks, months, years, and so on between two dates. The screenshot below shows how to count days between dates step by step, and the more options you can also calculate.
After enabling the feature by clicking Kutools > Formula Helper > Date & Time Helper, please do as follows:
- Choose Difference in the Type section;
- Select cell C2 and cell C3 separately in Date1 and Date2 textbox;
- Choose day from the Output result type drop-down list ;
- Click OK.
- To apply the Date & Time Helper feature, you should install Kutools for Excel first. Kutools for Excel offers a 30-day free trial with no limitations, go to download and install it now!
- Apart from calculating the difference between two dates, the Date & Time Helper feature also supports other date and time calculations. To add the date and time, check the Add option in the Type section. To calculate age based on a given date, check the Age option in the Type section.
Using DAYS function
Another way for getting the number of days between two dates is by using the DAYS function, which is introduced in Excel 2013. Here is the generic formula of the DAYS function:
Step 1: Input the DAYS formula
In cell C6, apply the following formula, then press the Enter button.
=DAYS(C3,C2)
Result
180 days are between the two dates in cells C3 and C2.
Using DATEDIF function
The fourth method of counting numbers of days between two dates is using the DATEDIF function. Here is the generic formula for calculating day difference between two dates:
Step 1: Input the DATEDIF formula
In cell C6, apply the following formula, then press the Enter button.
=DATEDIF(C2,C3,"d")
Result
180 days are between the two dates in cells C3 and C2.
- Unlike the other 3 methods, the DATEDIF formula doesn’t support subtracting a newer date from an older date. When the End_date is older than the Start_date, a #NUM! error will be returned.
- Different from the subtraction formula and DAYS function which can only count days between days, DATEDIF also can calculate months or years between two dates.
- DATEDIF is not included in the list of functions in Excel, which means it is an undocumented function. This requires you to manually input all the arguments to create a DATEDIF formula in your worksheet.
Count days between today and a date
If you want to calculate the number of days between today and a specific date, Excel’s built-in TODAY function can help you get the job done easily and quickly. Here are the two generic formulas to count days between today and another date with TODAY function:
To calculate the number of days between a past date and today:
To calculate the number of days between a future date and today:
In this case, you have a future date in cell C2, and today is 15-Mar-2023. You need to calculate the number of days between today and this date, please do as follows.
Step 1: Input the formula
In cell C5, apply the following formula, then press the Enter button.
=C2-TODAY()
Step 2: Format result as general
Select the result cell, click the Home tab, go to the Number group, then select General from the Number Format drop-down list.
Note: Or on the Home tab, click the Dialog Box Launcher next to Number. Then click General in the Category box.
Result
As you can see, there are 108 days between the today and the date in cell C2.
Note: The TODAY function will give the current date exactly. So, when a specific date is fixed, you will find that the number of days returned in the worksheet varies because the TODAY function changes from day to day.
Count working days between two dates
From time to time, we may need to calculate the number of days without the weekends. Here we will show you how to count working days between two dates with the help of NETWORKDAYS function in two situations. One situation is counting workdays without additional holidays provided. The other situation is counting workdays with additional holidays provided.
Here is the generic formula of NETWORKDAYS function:
Excluding weekends
For example, you have the Start date in cell C2 and the End date in cell C3, you want to find out the number of working days between the two dates regardless of the holidays.
Step 1: Input the NETWORKDAYS formula
In cell C6, apply the following formula, then press the Enter button.
=NETWORKDAYS(C2,C3)
Result
You can see there are 130 working days between the two dates, not considering holidays.
Excluding weekends and holidays
Unlike the example above, you also have the additional holidays listed in the data range C5:C8, and you need to calculate the number of working days between the two dates with the holidays provided.
Step 1: Input the NETWORKDAYS formula
In cell C6, apply the following formula, then press the Enter button.
=NETWORKDAYS(C2,C3, E3:E7)
Result
You can see there are 125 working days between the two dates, considering the holidays provided.
Note: In most of the world, the weekend is Saturday and Sunday. Then the NETWORKDAYS function is the exact function you need. But if your weekend is not Saturday and Sunday (like Sunday only), you should use the NETWORKDAYS.INTL function, which allows you to specify which days of the week should be considered weekends.
Related articles
How to check if a date is public holiday and count days except holidays in Excel?
For example, you have a list of dates and want to check if any public holidays exist in the list, how could you get it done quickly? In this article I will show you the ways to calculate all dates of American public holidays within given years, and then check if a date is an American public holiday in Excel easily.
How to calculate days left in month or year in Excel?
For certain case, you may want to know the number of remaining days in a month or a year. Please know more details from this article.
How to calculate days hours and minutes between two dates in Excel?
Supposing, you have two columns of date time cells, and now, you want to calculate the difference in days, hours and minutes between these two date time cells as following screenshot shown. This article, I will talk about some methods to solve this task for you.
How to calculate days until next birthday in Excel?
If you want to know how many days remaining until your next birthday, how would you do? Actually, if you are using Microsoft Excel, you can use formulas in the worksheet to easily count the days until next birthday. Please browse for more details as below tutorial shows.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Table of contents
- Video: Count days between dates
- Calculate days between two dates
- Using subtraction
- Using Kutools to count days, weeks...
- Using DAYS function
- Using DATEDIF function
- Count days between today and a date
- Count workdays between two dates
- Excluding weekends
- Excluding weekends and holidays
- Related articles
- The Best Office Productivity Tools
- Comments