How to Calculate the Difference Between Dates in Excel

Dates in Excel look simple on the surface — but calculating the gap between two of them opens up a surprising number of options. Whether you need the number of days between two events, the exact months elapsed, or a broken-down result showing years, months, and days separately, Excel has more than one way to get there. Which approach works best depends on what you're actually trying to measure.

Why Date Math in Excel Isn't Just Subtraction

Excel stores dates as serial numbers — each date is actually an integer counting days from January 1, 1900. That means subtracting one date from another gives you the number of days between them, which is often exactly what you need.

But if you want the difference expressed in months, years, or a combination, simple subtraction falls short. That's where Excel's date functions come in.

Method 1: Simple Subtraction for Day Counts

The most straightforward approach:

=B2-A2 

If A2 is your start date and B2 is your end date, this returns the number of days between them. One important step: format the result cell as a Number, not as a Date. Excel will sometimes auto-format it as a date, which produces a confusing result like "Jan 5, 1900" instead of the number 5.

This method is reliable for:

• Counting days remaining until a deadline
• Calculating age in days
• Measuring elapsed time between two logged events

Method 2: The DATEDIF Function 📅

DATEDIF is Excel's dedicated date-difference function. It's technically a legacy function — inherited from Lotus 1-2-3 — which is why it doesn't appear in Excel's autocomplete suggestions. You have to type it manually.

Syntax:

=DATEDIF(start_date, end_date, unit) 

The unit argument is what makes this function versatile:

Example: To calculate someone's age as of today:

=DATEDIF(A2, TODAY(), "Y") 

To display age as "32 years, 4 months, 17 days," you'd combine three separate DATEDIF formulas:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days" 

One caveat: DATEDIF can return incorrect results with the "MD" unit in certain edge cases involving months with different lengths. It's worth double-checking results when precision matters.

Method 3: DAYS, YEARFRAC, and Other Built-In Functions

Excel also provides more transparent alternatives:

DAYS(end_date, start_date) — Returns the number of days between two dates. Note the argument order is reversed compared to most functions.

=DAYS(B2, A2) 

YEARFRAC(start_date, end_date) — Returns the difference as a decimal fraction of a year. Useful for financial calculations or prorated values.

=YEARFRAC(A2, B2) 

A result of 2.5 means two and a half years elapsed. You can round this or multiply it as needed.

NETWORKDAYS(start_date, end_date) — Counts only working days between two dates, automatically excluding weekends. An optional third argument lets you exclude holidays.

=NETWORKDAYS(A2, B2, holiday_range) 

This is particularly useful for project timelines, payroll calculations, or any scenario where weekends don't count.

Handling Common Formatting Problems 🔧

A frequent source of confusion: dates stored as text. If your dates were imported from another system or copy-pasted, Excel might treat them as plain text rather than actual date values. In that case, date math returns an error.

Signs your dates are stored as text:

• They're left-aligned in the cell (dates normally right-align)
• Formulas return #VALUE! errors
• The cell shows a small green triangle in the corner

To convert text to dates, you can use DATEVALUE(), the Text to Columns tool, or retype the dates in a recognized format like MM/DD/YYYY.

Also worth noting: regional date format differences matter. A date that reads as March 4 in one locale reads as April 3 in another. If you're sharing spreadsheets across regions, confirming the date format is consistent prevents silent calculation errors.

Variables That Shape Which Method You Should Use

The right approach shifts depending on several factors:

• What unit you need — Days, months, years, or working days each call for different functions
• Whether partial periods matter — DATEDIF's "Y" unit counts only complete years; YEARFRAC includes the fractional part
• Data source — Dates from external imports or CSV files are more likely to be stored as text, which changes your prep work
• Excel version and platform — Most of these functions work across Excel versions, but behavior can vary slightly between Excel for Windows, Excel for Mac, and Excel Online
• Precision requirements — For casual tracking, simple subtraction is fine; for legal, financial, or HR calculations, the exact counting method (including how month-end edge cases are handled) can matter significantly

Working Day Calculations vs. Calendar Day Calculations

These two approaches produce meaningfully different results and serve different purposes. A project that spans 14 calendar days might only include 10 working days. Using the wrong type of calculation in a deadline-sensitive context leads to real errors.

NETWORKDAYS handles the working-day version, but it assumes a Monday–Friday work week. If your organization uses a different schedule, NETWORKDAYS.INTL lets you define which days are considered weekends.

The Part That Depends on Your Situation

Excel gives you a well-stocked toolkit for date calculations — but the formulas that make sense for a leave-management tracker are different from the ones used in a financial model or a simple project deadline list. The unit of measurement, the need for working-day logic, how your source data is formatted, and how you want results displayed all feed into which combination of functions actually fits. That answer lives in the details of your specific spreadsheet.