How to Calculate Date Difference in Excel

Calculating the difference between two dates is one of the most practical things you can do in Excel — whether you're tracking project timelines, calculating employee tenure, monitoring subscription periods, or figuring out how many days remain until a deadline. Excel offers several methods to do this, and the right approach depends on what unit of time you need and how precise the result should be.

The Simplest Method: Basic Subtraction

Excel stores dates as serial numbers — sequential integers starting from January 1, 1900. This means you can subtract one date from another using a plain formula.

If cell A2 contains a start date and B2 contains an end date:

=B2-A2 

This returns the number of days between the two dates. One important note: format the result cell as a Number, not a Date. If the result displays as a date rather than a number, select the cell, go to Format Cells, and choose General or Number.

This method works perfectly when you only need days and don't need to account for months or years separately.

Using the DATEDIF Function for Years, Months, and Days 📅

The DATEDIF function is Excel's dedicated date-difference tool. It's technically an undocumented legacy function (carried over from Lotus 1-2-3), but it works reliably in all modern versions of Excel.

Syntax:

=DATEDIF(start_date, end_date, unit) 

The unit argument controls what the function returns:

To express a date difference as "X years, X months, X days," you can combine three DATEDIF formulas:

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

This produces a readable result like "3 years, 4 months, 12 days" — useful for HR records, age calculations, or contract durations.

Important: DATEDIF requires the start date to be earlier than the end date. Reversing them will return an error.

Calculating Working Days with NETWORKDAYS

When business context matters — project deadlines, payment terms, SLA tracking — you typically want to exclude weekends. The NETWORKDAYS function handles this automatically.

=NETWORKDAYS(start_date, end_date) 

This returns the number of working days (Monday–Friday) between two dates, including both the start and end dates in the count.

You can also exclude public holidays by adding a third argument — a range of cells containing holiday dates:

=NETWORKDAYS(A2, B2, holidays_range) 

For schedules that use non-standard work weeks (e.g., Sunday–Thursday), NETWORKDAYS.INTL lets you specify which days are considered weekends using a weekend code or a custom string.

Using DAYS, DAYS360, and YEARFRAC for Specific Scenarios

Several other functions cover more targeted use cases:

• DAYS(end_date, start_date) — A straightforward function that returns the number of days between two dates. Note the argument order is reversed compared to most date functions.
• DAYS360(start_date, end_date) — Calculates the difference based on a 360-day year (12 months of 30 days each), commonly used in financial and accounting contexts such as bond calculations.
• YEARFRAC(start_date, end_date) — Returns the fraction of a year represented by the date range. Useful when you need a decimal representation, such as 2.75 years rather than "2 years, 9 months."

Handling TODAY() for Dynamic Calculations ⏱️

If you want a formula that updates automatically — for example, always showing how many days have passed since a project started — use the TODAY() function as one of the date arguments:

=TODAY()-A2 

Or with DATEDIF:

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

These formulas recalculate every time the workbook opens or refreshes, keeping your figures current without manual updates.

Common Issues That Affect Results

A few factors consistently trip people up when working with date differences in Excel:

• Dates stored as text — If Excel doesn't recognize a cell as a date (often visible by left-alignment), subtraction and DATEDIF will return errors. Use DATEVALUE() to convert text strings to proper date serial numbers.
• Regional date formats — Date interpretation can differ between locales (MM/DD/YYYY vs. DD/MM/YYYY). A date that looks correct visually may be parsed differently depending on your system's regional settings.
• Negative results — Basic subtraction will return a negative number if the start date is later than the end date. This is valid mathematically, but DATEDIF will throw a #NUM! error in the same scenario.
• Excel version differences — NETWORKDAYS.INTL and YEARFRAC are available in Excel 2007 and later. Very old workbooks or heavily restricted environments may behave differently.

The Variables That Shape Which Method Fits

Which approach makes sense depends on more than just knowing the formulas. The unit of time you need (days vs. months vs. years vs. working days), the purpose of the calculation (financial modeling, HR tracking, project management), and how the output will be used (displayed to users, fed into other formulas, exported to reports) all change which function is the right tool.

Someone calculating loan interest accrual has different needs than someone building a staff onboarding tracker or a project deadline dashboard. Even within a single spreadsheet, different columns might call for DATEDIF, NETWORKDAYS, and basic subtraction — each doing a distinct job. 🗓️

The formulas are consistent and reliable across modern Excel versions, but the choice between them isn't universal — it comes down to what your specific data represents and what you need the result to do.