Excel Calculates Difference Between Two Dates in Days (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

To keep track of an employee/student to check the progress, you may need to know the difference between two dates in days in Excel. Also, counting days is most important for tracking projects to meet a deadline.

Most of the time for time management, you need to know the difference between two dates. In our regular life, there are so many purposes for which we need to know the days between two dates.

In this article, we will see how Excel calculates the difference between two dates in days. Below, we have attached an overview image so that you can understand today’s learning.

How to Find Difference Between Two Dates in Days in Excel


Excel Calculates Difference Between Two Dates in Days: 7 Ways

We’ll show you 7 quick methods to calculate the difference between two dates in days in Excel. To explain our methods, we’ve picked a dataset with 3 columns: “Name”, “Joining Date”, and “Resigning Date”. In all our methods, we’re gonna find the number of days each employee worked in a company.


1. Using DATEDIF Function to Find Difference Between Two Dates in Days

For the first method, we’re going to use the DATEDIF function to calculate the difference between two dates. This function is not listed in Excel, hence, no tooltip will be displayed for this function. We’ve added a column Total Days in our dataset.

Using DATEDIF Function to Find Difference Between Two Dates in Days

  • Type the following formula in cell E5 & press ENTER.
=DATEDIF(C5,D5,"d")

The syntax of this function is DATEDIF(start_date,end_date,unit). Our initial date is on cell C5, the end date is on cell D5. And finally, we’re setting our output format to days using d in the function.

We’ll see the date difference in days in cell E5.

Use of Fill Handle icon to autofill the formula

Get the total days of all cells

Here you will see all the total days.


Calculating Days Ignoring Years from Dates

Here, we have used the following formula in cell E5.

=DATEDIF(C5,D5,"Yd")

Our initial date is on cell C5, the end date is on cell D5. We’re setting our output format to days using Yd in the function. So, the years will not be considered here. Which means days are counting from 01/March to 10/March.

Calculating Days Ignoring Years from Dates

We have used the same formula for the rest of the cells.

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


2. Use of DAYS Function to Find Date Difference in Days

For the second method, we’ll use the DAYS function to get the date difference in days.

  • First, type the following formula in cell E5.
=DAYS(D5,C5)

The syntax of this function is DAYS(end_date,start_date). Here, our end date is on cell D5 and the initial date is on cell C5.

Use of DAYS Function to Find Date Difference in Days

  • Finally, press ENTER and AutoFill the formula.

We can see this is the simpler version of the DATEDIF function.


3. Calculating Difference Between Two Dates in Days Using DAYS360 Function

In this section, we’re going to use the DAYS360 function to find the difference between two dates in days.

  • Type the following formula in cell E5.
=DAYS360(C5,D5)

The syntax of this function is DAYS360(start_date,end_date). Here, our initial date is on cell C5 and the end date is on cell D5.

Difference Between Two Dates in Days Using DAYS360 Function

  • Press ENTER to get the result.
  • AutoFill the formula for the rest of the cells.
Note: This function assumes that every month consists of 30 days. Therefore, we’ll see some differences in the output from methods 1 and 2.

Read More: Excel Formula to Count Days from Date


4. Using DATE Function to Find Days Between Two Dates

For the fourth method, we’ll use the YEAR, MONTH, DAY, and DATE functions to calculate the difference between two dates in Excel.

  • Type the following formula in cell E5 >> Press ENTER.
=DATE(YEAR(D5),MONTH(D5),DAY(D5))-DATE(YEAR(C5),MONTH(C5),DAY(C5))

Using DATE Function to Find Days Between Two Dates

Formula Breakdown

Our formula has two DATE functions. We’re subtracting the first date from the last date using those.

  • YEAR(D5),MONTH(D5),DAY(D5): will return 2022, 5, 10 respectively.
  • Now the first part of our formula reduces to -> DATE(2022,5,10)
    • Output: 44691.
    • Our output will be in Serial Number format.
  • Our second portion will return 44621.
  • Finally, we’re subtracting it to get our date difference (44691-44621=70).
  • AutoFill the formula for the rest of the cells.

Thus, we’ve found the difference between the two dates in day format.

Read More: How to Calculate 90 Days from a Specific Date in Excel


5. Calculate Workdays Between Two Dates in Days in Excel

In the case of office dealing, you may need to exclude the weekends and general holidays from the total days. Basically, we’re talking about how you can calculate the workdays only.

To do so, there are two functions in Excel. One is the NETWORKDAYS function. And the other is the NETWORKDAYS.INTL function.

There is a basic difference, in the case of the NETWOKDAYS function, the weekends will be Saturday and Sunday. Where NETWORKDAYS.INTL function can take weekends as an argument, so you can use different weekends.


5.1 Use of NETWORKDAYS Function to Count Workdays

In this method, we’re going to use the NETWORKDAYS function to find date differences in the day’s format. We’ve changed our dataset a little bit by adding 2 holidays.

Use of NETWORKDAYS Function to Count Workdays

  • Type the following formula in cell E5.
=NETWORKDAYS(C5,D5,$C$13:$C$14)

The syntax of this function is NETWORKDAYS(start_date, end_date, [holidays]). Here, our initial date is on cell C5, and the end date is on cell D5 and our holidays are in the range C13:C14. Moreover, remember to use absolute cell references in this function.

  • Press ENTER.

Finally, AutoFill the formula. In conclusion, we’ll find the workdays in days between the dates.


5.2 Use of NETWORKDAYS.INTL Function

If you want to change the weekends, you need to use NETWORKDAYS.INTL function.

Here, you can choose the weekend according to your preference.

The syntax of this function is NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]).

After giving the 2nd comma in the function, you will get a list to select the weekends. You can select any of them. Additionally, you have to create a holiday list.

Weekends list in NETWORKDAYS.INTL Function

So, the final formula is given below.

=NETWORKDAYS.INTL(C5,D5,1,$C$13:$C$26)
  • Press ENTER to get the result >> Use the Fill Handle icon for the other cells.

Use of NETWORKDAYS.INTL Function for Counting Workdays


6. Find the Difference Between Two Dates in Days in Excel Using a Generic Formula

In this section, we’ll use a generic formula to calculate the date differences in days.

  • Select the range E5:E10 >> type the following formula.
=D5-C5

Here, we’re just simply subtracting the end date from the starting date.

  • Press CTRL + ENTER to AutoFill our formula.

Find Difference Between Two Dates in Days in Excel Using a Generic Formula


7. Use of TODAY Function to Find Difference Between Current and Another Date in Days

For the last method, we’re gonna use the TODAY function and subtract the initial date value from it to find our date difference. We’ve removed a column from our dataset.

  • Select the range E5:E10 >> Type the following formula.
=TODAY()-C5

Use of TODAY Function to Find Total Days

Here, we’re just simply subtracting the end date from today’s date. Therefore, your value will not be the same when you open this file. Moreover, if you have a large dataset, you should avoid using this function, as it will slow down your computer.

  • Finally, press CTRL + ENTER.

This will Auto-fill our formula. Thus, we’ve shown you yet another formula to find the difference between two dates in days format in Excel.

Read More: Calculate Number of Days between Two Dates with VBA in Excel


How to Calculate Difference Between Two Dates in Years/Months/Weeks in Excel

You can also calculate the difference between two dates in years/months/weeks. Here, we’re going to use the DATEDIF function for this purpose. This function is not listed in Excel, hence, you have to write the whole formula manually.

Now, we will find the difference between two dates in years, months, and weeks in columns E, F, and G respectively.

Calculate Difference Between Two Dates in Years/Months/Weeks in Excel

  • To find the years only, use the following formula in cell E5.
=DATEDIF(C5,D5,"y")
  • To find the total months, use the below formula in cell F5.
=DATEDIF(C5,D5,"m")
  • To calculate the weeks, write the corresponding formula in cell G5.
=DATEDIF(C5,D5,"d")/7

Actually, in the inverted comma, the DATEDIF function takes the argument for showing output.

Read More: How to Calculate Number of Months Between Two Dates in Excel


How to Calculate an Entire Period in Excel

Furthermore, you can calculate an entire period of two given dates.

  • To do so, use the below formula.
=DATEDIF(C5,D5,"y") & " Years, " & DATEDIF(C5,D5,"ym") & " Months, " & DATEDIF(C5,D5,"md") & " Days"

How to Calculate an Entire Period in Excel

Formula Breakdown

  • DATEDIF(C5,D5,”y”) —> returns the difference between D5 and C5 dates in years.
    • Output: 3.
  • DATEDIF(C5,D5,”ym”)—> gives the number of months between D5 and C5 ignoring the years.
    • Output: 0.
  • DATEDIF(C5,D5,”md”)—> calculates the total days between D5 and C5 ignoring the months.
    • Output: 9.
  • Ampersand (&) sign will join 3 with Years, 0 with Months, and 9 with Days.

In this way, you can also calculate the age of a person. You just need to use the TODAY function as the ending date and you must insert the date of birth as the starting date. The rest of the formula will be the same.

Read More: How to Find Number of Weeks Between Two Dates in Excel


Calculator for Counting the Days Between Two Dates in Excel

Here, we have attached a sheet named Calculator. With the help of this Excel sheet, you can calculate the difference between any two dates in days.

In that sheet, you need to insert the starting date in cell B5 and the ending date in cell B9. Then get the total days in cell D7.

Calculator for Counting the Days


Practice Section

We’ve added practice datasets for each method in the Excel file.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

We’ve shown you 7 methods of how Excel calculates the difference between two dates in days. However, If you face any difficulties, feel free to comment below for assistance. Thanks for reading, keep excelling!


Related Articles


<< Go Back to Days Between Dates | 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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo