How to Calculate the Difference Between Two Dates in Excel?

How to Calculate the Difference Between Two Dates in Excel?

5 mins read1.2K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 6, 2024 17:27 IST

Calculating the difference between two dates is a very easy yet essential task in MS Excel. Whether you are a student, a senior manager with an MNC or a startup founder, you need to calculate the difference between 2 dates in Excel. Whether you need to determine the number of days between two events, measure the duration of a project, or calculate an individual’s age, Excel offers a range of functions and formulas to simplify this process. Learn how to calculate the date difference in MS Excel using simple subtraction and powerful date functions. Master the art of time calculations for efficient data analysis and planning.

2023_08_How-to-Calculate-Difference-Between-2-Dates-in-Excel.jpg

Even though Knowing how many days there are between two dates is a simple job with a simple subtraction between the start and end dates. The matter gets more complicated if you want to count the start and end days or if you want to count from the day after the start date and up to the day before the end date. For this reason, when counting the days between two dates, one should always consider whether to count from the first day or the next.

Content

Calculate Days Between Two Dates

As we already pointed out, a simple subtraction calculates the days between two dates in Excel.

Here you can see that you can get the number of days by subtracting the later date from the earlier date.

But what if you want to calculate Years, months, and dates in Excel?

There is a function, DATEDIF(), which calculates the difference between two dates in Excel.

Embark on your journey in MS Excel with our guide to the best colleges, innovative programs, online courses, and career opportunities!

This function uses the following syntax:

=DATEDIF(Start_Date, End_Date, Metric)

where:

  • Start_Date: The start date
  • End_Date: The end date
  • Metric: The metric to calculate, such as:
    • “d”: Days
    • “m”: Months
    • “y”: Years

Please note this function won’t automatically appear. You would need to type =DATEDIF( into the required cells. 

Let us understand this using examples – 

In the below table, we seek to calculate the number of days between two sets of dates.

150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more
What Are Excel Errors and How To Fix Them?
What Are Excel Errors and How To Fix Them?
Excel is a powerful tool for working with data, but it can also be frustrating when errors occur. While some errors are easy to fix, others can be more difficult...read more

1. Calculating the Difference of Years

To calculate the gap of years between the two dates, we will use the formula –

=DATEDIF(Start_Date, End_Date, “Y”)

Since we have our data in Cells B4 and C4, we will take 0

  • Start_Date = B4 
  • End_Date = C4
  • Variable = year, denoted as “Y”

So our formula is –

=DATEDIF(B4,C4,”y”)

Here is the step-by-step method to represent how it works.

  • Go to the cell you want your result to appear in. 
  • Apply formula =DATEDIF(B4,C4,”y”).
  • Press Enter.
  • You will see the result appearing in the selected cell.
  • Use the Drag and Drop in Excel option or “Fill Handle” to fill in the rest of the cells.
  • You will see the results for the respective dates against their cells.
What is Filter in Excel?
What is Filter in Excel?
Filter in Excel is a valuable tool to analyze data quickly. In this article, you will learn how to use filters in Excel.
Difference Between Formula and Function in Excel
Difference Between Formula and Function in Excel
Formula and Function in Excel sound similar and are often confusing. A formula is a user-created expression that combines operators, cell references, and constants to perform customized calculations or data...read more

2. Calculating the Difference of Months

We will use the same function =DATEDIF(Start_Date, End_Date, Metric)

where:

  • Start_Date: B4
  • End_Date: C4
  • Metric: “m”: Months 

Our formula is –

=DATEDIF(B4,C4,”m”)

  • Go to the cell you want your result to appear in. 
  • Apply formula =DATEDIF(B4,C4,”m”).
  • Press Enter.
  • You will see the result appearing in the selected cell.
  • Drag and Drop to fill in the rest of the cells.
  • The results for the respective dates will appear against their cells.
What is a CSV File, and How to Convert CSV to Excel?
What is a CSV File, and How to Convert CSV to Excel?
Learn what is a CSV file and how to create one. Also learn how to convert CSV to Excel.
How To Add Drop Down In Excel In Less Than 1 Minute?
How To Add Drop Down In Excel In Less Than 1 Minute?
Learn how to create dropdown lists in excel in less than 1 minute through example and implement an error message.

3. Calculating the Difference of Days

Same function again!

=DATEDIF(Start_Date, End_Date, Metric)

where:

  • Start_Date: B4
  • End_Date: C4
  • Metric: “d”: Days 

Our formula is –

=DATEDIF(B4,C4,”d”)

  • Go to the cell where you want your result to appear. 
  • Apply formula =DATEDIF(B4,C4,”d”).
  • Press Enter.
  • You will see the result appearing in the selected cell.
  • Drag and Drop to apply the formula to the rest of the cells.
  • The results for the respective dates will appear against their cells.

Additional Tips 

  • Excel, by default, counts from the day after the initial date, so if we need to count from the initial date, we will have to add one day.
  • If you are wondering why you are not getting the correct result despite using the right formula, check the cell format like this.

If you find any other format, change it to General for correct results. I made the same mistake, but after researching, I realized I used the incorrect format.

I hope this blog was useful to you!

FAQs

How can I subtract two dates in Excel to find the difference in days?

To calculate the difference in days between two dates. e.g., start date in cell A1 and end date in cell B1, use the formula =B1 - A1.

What if the result of the date subtraction shows as a strange number, not the expected days?

Ensure the cell containing the result is formatted as a number or general. Sometimes, Excel may display the result as a date due to formatting.

Can we exclude weekends or specific holidays from the date difference calculation?

You can use more advanced formulas or custom functions to exclude weekends or specific holidays from the date difference calculation.

How do I handle negative date differences?

A negative date difference occurs when the start date is later than the end date. Excel represents dates as serial numbers, and subtracting a larger serial number from a smaller one results in a negative value.

Can Excel calculate the difference between two dates in working days?

Yes, you can use a formula or custom function that excludes weekends and holidays to calculate the difference between two dates in working days.

What if my dates are in a different format, and Excel doesn't recognize them?

Ensure that your dates are formatted correctly. Excel may not recognize dates if entered as text or in a format not recognized by date system of MS Excel.

Can I use the date difference calculation in complex date-related formulas?

The date difference calculation is a fundamental building block in Excel for handling various date-related functions and complex formulas.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments