How to Calculate the Number of Days Between Dates in Excel

Excel has several ways to count days between two dates — and which method works best depends on what you're actually trying to measure. Simple subtraction, dedicated functions, and specialized formulas each serve different purposes, and understanding the differences saves a lot of frustration.

The Simplest Method: Direct Subtraction

At its core, Excel stores dates as serial numbers — integers counting from January 1, 1900. This means you can subtract one date from another just like any other number.

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

=B2-A2 

This returns the number of days between them. The catch: Excel may format the result as a date rather than a number. If you see something like "1/5/1900" instead of "5," just format the cell as Number or General.

This method is fast and reliable for straightforward day counts.

Using the DAYS Function

Excel 2013 and later includes the DAYS function, which makes the intent of your formula explicit:

=DAYS(end_date, start_date) 

Note the argument order — end date comes first. This trips people up regularly. The result is a signed integer, so if your end date is earlier than your start date, you'll get a negative number.

DAYS handles the same calculation as subtraction but is easier to read inside complex formulas and makes auditing spreadsheets simpler.

The DATEDIF Function: Flexible but Hidden 📅

DATEDIF is one of Excel's undocumented legacy functions — it works in all modern versions but won't appear in autocomplete suggestions. It calculates the difference between two dates in a unit you specify.

=DATEDIF(start_date, end_date, unit) 

The unit argument controls what you get back:

For a straight day count, "D" gives the same result as subtraction. Where DATEDIF becomes genuinely useful is when you need to break a time span into years, months, and days simultaneously — for things like age calculators or contract duration displays.

A common pattern for displaying a full duration:

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

One important note: DATEDIF requires the start date to come first. Unlike DAYS, it won't return a negative number if dates are reversed — it returns an error. Always validate that your start date is earlier than your end date when using this function.

Counting Days from Today

A frequent use case is measuring how many days have passed since an event, or how many days remain until a deadline. The TODAY function returns the current date and updates automatically each time the spreadsheet recalculates.

Days elapsed since a past date:

=TODAY()-A2 

Days remaining until a future date:

=A2-TODAY() 

These are live calculations — useful for project trackers, expiration monitoring, or any dashboard where dates change relative to the present.

Accounting for Weekdays Only: NETWORKDAYS

If you need to count working days rather than calendar days, direct subtraction won't serve you. The NETWORKDAYS function excludes weekends automatically:

=NETWORKDAYS(start_date, end_date) 

It also accepts an optional third argument for custom holidays — a range of cells containing dates you want excluded from the count.

=NETWORKDAYS(A2, B2, holidays_range) 

For situations where the work week doesn't run Monday–Friday, NETWORKDAYS.INTL lets you define which days count as the weekend using a number code or a custom string pattern.

Common Issues That Affect Results 🔧

Date formatting vs. actual dates: If Excel is treating a date as text, formulas return errors or zero. You can test this by selecting a cell and checking whether the formula bar shows a date or a text string. Dates should be right-aligned in cells by default; text-formatted dates often left-align.

Regional date formats: Dates entered as text in formats Excel doesn't recognize for your locale won't behave as date serial numbers. Converting them using DATEVALUE or Text to Columns resolves this.

Time components: If your dates include timestamps (e.g., "6/15/2024 14:30"), subtraction returns a decimal number, not a clean integer. Wrapping the result in INT() strips the time portion when you only want full days.

Which Variables Shape Your Approach

The right method depends on factors specific to your spreadsheet:

• Excel version — DAYS is unavailable before Excel 2013; NETWORKDAYS.INTL requires Excel 2010+
• Whether weekends matter — payroll, project timelines, and SLA tracking often require workday-only counts
• Whether you need the result broken into units — displaying "2 years, 3 months, 15 days" requires DATEDIF; a simple day count doesn't
• Data source quality — dates imported from other systems frequently arrive as text and need conversion before any formula works correctly
• Whether the comparison is static or rolling — fixed historical ranges behave differently from calculations anchored to TODAY()

The gap between knowing these methods and knowing which one fits your spreadsheet comes down to understanding your own data structure, your audience for the output, and how the sheet will be maintained over time.