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

• Understanding Date Formats in Excel
• Calculate Days Between Two Dates
• Examples
  • Calculating the Difference of Years
  • Calculate the Difference of Months
  • Calculating the Difference of Days
• Additional Tips
• Potential Errors in Date Calculations in Excel and How to Correct Them

Understanding Date Formats in Excel

How Excel Stores Dates

Excel does not store dates as they appear (such as 01/01/2024). Instead, it stores every date as a serial number. This corresponds to the number of days that have passed since January 1, 1900, which is day 1.

For instance:

January 1, 1900 = 1

January 2, 1900 = 2

January 1, 2024 = 45292

Using this feature, MS Excel can calculate dates or determine the number of days between two dates.

Why is Correct Date Formatting Important?

Having the correct date format is essential for several reasons:

Accurate Calculations: Incorrectly formatted dates can be interpreted by Excel as plain text, and you cannot subtract or compare them.

Avoiding Errors: Incorrect formats can be confusing. For example, 03/04/2025 can be read as March 4 or April 3, 2025, depending on the format applied. It is important that you choose the correct region for the right date format to appear. To check or change your regional settings:

• On Windows: Go to Control Panel > Region > Formats.

• On Mac: Go to System Preferences > Language & Region.

Consistency: A consistent date format makes data more readable and reduces errors, particularly when collaborating with other individuals or teams and exchanging files.

Function Compatibility: You need correct date values to work with Excel functions like DATEDIF, TODAY(), or NETWORKDAYS().

Pro Tip: To verify whether a cell contains a valid date, try changing the format to "Number." If it displays a serial number, it is showing the actual date. If not, it's likely text.

Calculate Days Between Two Dates

As we already pointed out, a simple subtraction calculates the date difference 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 where you want your result to appear. 
• 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 where you want your result to appear. 
• 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 in 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: Excel will factor in hours and minutes if your date cells include time. 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, such as 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.

Potential Errors in Date Calculations in Excel and How to Correct Them

Below are some common errors that may occur while calculating dates in Excel. Also learn how to fix those errors.

1. #VALUE! Error: This error often occurs when Excel can't recognize the date format.

Cause: The date_text argument in a function like DATEVALUE is invalid, is not a date, or is a text/number. The date format in the formula doesn't match the system's date and time settings.

Solution: Ensure the date_text argument is a valid date format. Right-click the cell, select "Format Cells" (or press CTRL+1), and ensure it's formatted as Text1. Also, verify that your system's date and time settings match the date format used in the formula.

2. Date outside acceptable range: Excel dates must be between January 1, 1900, and December 31, 9999. If the year argument is less than 0 or greater than 9999, a DATE formula will return the #NUM! Error.

Solution: Make sure that the date_text argument represents a date between January 1, 1900 and December 31, 9999.

#NUM! Error: This can occur if the year argument in the DATE function is less than 0 or greater than 9999.

Solution: Always supply four-digit years to avoid confusion.

3. Incompatible Date Systems

Problem: Excel has two date systems. The dates might be wrong if you copy dates between files using different systems.

Solution:

• In an empty cell, type 1462 and copy it.
• Select all the cells with the wrong dates.
• Paste Special: Choose "Values" and then either "Add" or "Subtract" to fix the dates. (Add to shift dates forward, subtract to shift them backwards)

4. Incorrect Date Interpretation: Excel interprets the year argument using the computer's date system. If the year argument is between 0 and 1899, Excel adds that number to 1900. For example, DATE(100, 12, 31) returns December 31, 2000 (1900 + 100).

Solution: Always input four-digit years to avoid confusion.

5. ###### Error: While not specific to date calculations, this error appears when a column isn't wide enough to display the entire value. It can also occur if a negative value is formatted as an invalid date.

Solution: Widen the column by double-clicking the right column border at the top of the worksheet. If that doesn't fix the issue, check the cell's format. If it's a Date format, change it to General, it should work.

6. DATEDIF Function Errors: The DATEDIF function may return a #VALUE! Error if the date format is not mm/dd/yyyy.

Solution: Use the mm/dd/yyyy 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!