How to Calculate the Difference Between Two Dates in Excel?

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.

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
• Examples
  • Calculating the Difference of Years
  • Calculate the Difference of Months
  • Calculating the Difference of Days
• Additional Tips

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.

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.

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.

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

1. Counting from the Initial Date: By default, Excel calculates the difference from the day after the initial date. To include the initial date, add +1 to your formula.
2. Check Cell Format: If your formula isn't returning the correct result, ensure the cells containing dates are formatted as "Date" and not as "Text" or other formats.
3. Use Absolute References: When working with fixed dates in your formula, use $ to lock the cell reference (e.g., $A$1) if you plan to copy the formula elsewhere.
4. Account for Time: If your date cells include time, Excel will factor in hours and minutes. Use the INT function (e.g., =INT(A2-B2)) to get the whole number of days.
5. Include Working Days: Use the NETWORKDAYS function to calculate working days between two dates. Exclude weekends and optional holidays.
6. Combine Functions: You can combine functions for advanced calculations, such as using DATEDIF with IF statements to create customized outputs.
7. Double-Check Regional Settings: Date calculation in Excel depends on system settings like regional formats (e.g., DD/MM/YYYY or MM/DD/YYYY). Ensure consistency across all dates.

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.

Conclusion

Built-in functions in MS Excel make it easy to calculate date differences. These functions can offer the flexibility to handle different types of date-related tasks, such as calculating the number of days or months/years between two dates or even working days excluding weekends and holidays. This will help users streamline data analysis, enhance productivity, and ensure accuracy in projects involving schedules, deadlines, or timelines.

I hope this blog was useful to you!