Excel Formula for Number of Days Between Two Dates

To find the number of days between two dates in Excel, subtract the earlier date from the later one: enter =B2-A2 when A2 contains the start date and B2 contains the end date. The result is the elapsed number of days; use DATEDIF if you prefer a function specifically for date differences.

How date subtraction works

Excel stores dates as serial numbers, so subtraction works just like it does with ordinary numbers. A date represents a day on the calendar, and the difference between two date serials is the number of days separating them. You can type the dates directly into cells and use cell references in the formula, which makes the calculation easy to update when either date changes.

For example, suppose A2 contains January 1, 2025, and B2 contains January 10, 2025. With those dates in A2 and B2, enter =B2-A2. The result is 9, because nine midnights pass from the start of January 1 to the start of January 10. The dates are illustrative; the same subtraction works for dates in other years and in other date orders.

Use the later date first if you want a positive result. If the earlier date is in B2 and the later date is in A2, the subtraction returns a negative number. That can be useful for showing whether a deadline has passed, but if you only want a positive distance regardless of order, use an absolute-value calculation. With the earlier-or-later dates in A2 and B2, enter =ABS(B2-A2); for January 1 and January 10, the result is 9 either way.

Count both the start and end dates

A common source of confusion is whether “days between” includes the two dates themselves. Ordinary subtraction measures the elapsed gap and does not count both endpoints as whole calendar days. Thus January 1 to January 10 is 9 elapsed days. If a schedule, trip, or coverage period treats both January 1 and January 10 as included days, the count is 10.

With the inclusive start date in A2 and inclusive end date in B2, use =B2-A2+1. For January 1 through January 10, the result is 10. This convention is often useful for counting calendar dates in a range, but it is not automatically the right choice for every task. For example, a 10-day countdown from January 1 to January 11 is usually 10 days of elapsed time, not 11 inclusive dates. Decide whether the question is about elapsed time or dates covered before adding one.

If the dates can be entered in either order and you still need an inclusive count, you can use the smaller and larger dates to establish the range. With the two endpoint dates in A2 and B2, enter =ABS(B2-A2)+1. This returns 10 for January 1 and January 10, regardless of which date is in which cell. Only use this version when the dates are meant to define an inclusive range.

Using DATEDIF for a day count

DATEDIF returns the difference between a start date and an end date in a chosen unit. For days, its unit argument is "d". The function is available in Excel, although it may not appear in the formula autocomplete list. It is a practical alternative to subtraction when a formula already uses DATEDIF for years or months.

With the start date in A2 and the later end date in B2, enter =DATEDIF(A2,B2,"d"). If A2 is January 1, 2025, and B2 is January 10, 2025, the result is 9. Like subtraction, this counts elapsed days rather than both dates inclusively. The start date must be earlier than or equal to the end date; a reversed pair can produce an error, so ordinary subtraction is often clearer when dates may be out of order.

For a simple day difference, there is generally no accuracy advantage to choosing one method over the other. Subtraction is shorter and makes negative results possible, which can be informative. DATEDIF makes the unit explicit and can be convenient in multi-unit age or duration calculations. Neither method automatically excludes weekends or holidays.

Count working days instead of calendar days

If you mean weekdays, use NETWORKDAYS rather than subtracting dates. It counts working days from the start date through the end date, including both endpoints when they are workdays. By default, Saturday and Sunday are weekends. This difference in endpoint handling is important: a Monday-to-Friday range has five working days, even though the elapsed date difference is four.

With Monday, January 6, 2025, in A2 and Friday, January 10, 2025, in B2, enter =NETWORKDAYS(A2,B2). The result is 5. To exclude a holiday, put that holiday date in D2; with the same range in A2:B2 and the holiday in D2, enter =NETWORKDAYS(A2,B2,D2:D2). If D2 is Wednesday, January 8, the result is 4. For a list of holidays, use the full range containing those dates, such as D2:D10.

For a work schedule with weekend days other than Saturday and Sunday, Excel also has NETWORKDAYS.INTL, which lets you specify a weekend pattern. Check its weekend-code options before using it, especially for schedules that do not follow a standard Monday-to-Friday week. These workday functions count dates, not paid hours or partial shifts.

When the cells include times

Excel dates can include time-of-day values. A date with a time is stored as a fractional part of a day, so subtracting two date-and-time values can return a decimal. For instance, a difference of 1.5 means one and a half days, or 36 hours. If both cells contain midnight times, the result is a whole number as expected.

With a start timestamp in A2 and an end timestamp in B2, enter =B2-A2. If A2 is January 1 at 9:00 a.m. and B2 is January 2 at 9:00 a.m., the result is 1 day. If B2 is January 2 at 3:00 p.m., the result is 1.25 days, or 30 hours. To display the decimal, format the result as Number; to display a duration as hours and minutes, use a duration format such as [h]:mm. Square brackets allow elapsed hours to continue past 24 rather than restarting at zero each day.

If you want only the number of complete 24-hour periods in a timestamp difference, use the integer portion. With a start timestamp in A2 and an end timestamp in B2, enter =INT(B2-A2). For January 1 at 3:00 p.m. through January 3 at noon, the elapsed time is 1.875 days and the formula returns 1 complete 24-hour period. That is different from counting calendar-date boundaries, which would be two.

Make sure Excel recognizes both values as dates

A cell can look like a date without being a usable date value. Excel may store an imported date as text, particularly when the source uses a different regional order for day, month, and year. If subtraction returns #VALUE! or behaves unexpectedly, check whether each input is recognized as a date. Select a cell and change its number format; a genuine date will display in another date format, while text often stays unchanged.

On Excel for Microsoft 365 for Windows, select the result cell and choose Home > Number Format to display it as Number or General if it is showing as a calendar date. You can also open Format Cells with Ctrl+1. On Mac, Command+1 opens Format Cells; Excel for the web offers number-format choices on the Home tab, though its controls may be arranged differently. If a subtraction result appears as a date such as January 9, the calculation may be correct but the result cell is formatted as a date. Change its format to General or Number.

For reliable inputs, enter dates in a format Excel recognizes under your regional settings, or use Excel’s date picker where available. Avoid assuming that a text value such as 03/04/2025 means the same day in every locale: it can be interpreted as March 4 or April 3. Imported text may need conversion before calculation. Also check that blank cells are not being treated as intended dates; a blank reference can be interpreted as zero in arithmetic and produce a misleading result.

Choose the right calculation for the question

The right formula depends on what “number of days” means in context. Before building a workbook, decide whether you need elapsed days, an inclusive calendar-day count, working days, or complete 24-hour periods. A formula can be mathematically correct and still answer the wrong version of the question.

Elapsed calendar days: subtract the start date from the end date. This is usually the best default for time between two dates.
Inclusive calendar dates: add 1 to the subtraction when both endpoints should count.
Weekdays or workdays: use NETWORKDAYS, adding a holiday range if needed.
Elapsed time with timestamps: subtract the full date-time values; the result may be a decimal number of days.
Positive distance regardless of order: use ABS around the date subtraction, but do not do this if a negative result carries meaning.

For a changing “days since” value, Excel’s TODAY function returns the current date and updates when the workbook recalculates. With a fixed start date in A2, enter =TODAY()-A2; if A2 is January 1 and the current date is January 10, the result is 9. This formula changes over time, unlike a fixed end date typed into a cell. Use a fixed date instead when a report must remain reproducible as of a particular day.

Frequently Asked Questions

What is the simplest Excel formula for days between two dates?
Put the earlier date in A2 and the later date in B2, then use =B2-A2. The result is the elapsed number of calendar days.

Why does Excel show a date instead of a number?
The result cell is probably formatted as a date. Change its number format to General or Number; the underlying day calculation may already be correct.

How do I count both dates in the range?
With the start date in A2 and end date in B2, use =B2-A2+1. This includes both endpoints, so January 1 through January 10 returns 10.

How do I exclude weekends and holidays?
Use NETWORKDAYS with the start date, end date, and an optional range of holiday dates. It excludes Saturday and Sunday by default and counts qualifying workdays inclusively.

Can I calculate the difference when the dates are in reverse order?
Subtraction returns a negative number when the end date comes first. Use =ABS(B2-A2) if you need a positive distance in either order; use DATEDIF only when its start date is no later than its end date.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.