Excel Formula to Calculate Days Between Dates, Explained

To calculate elapsed days between two dates in Excel, subtract the earlier date from the later one. With a start date in A2 and an end date in B2, enter =B2-A2; for example, January 1 to January 10 returns 9 days. Excel also provides the DAYS function, and the right choice depends on whether you want elapsed days, an inclusive date count, or working days.

Subtract dates for the simplest result

Excel stores recognized dates as sequential serial numbers, so subtracting one date from another gives the number of days between them. This is usually the clearest method for calculating elapsed calendar days, and it works in Microsoft Excel for Microsoft 365 on Windows.

With the start date in A2 and the end date in B2, enter =B2-A2. If A2 contains January 1, 2026, and B2 contains January 10, 2026, the result is 9. That means nine full 24-hour date intervals separate the two dates, not ten dates counted inclusively.

To use the formula for more rows, enter the dates in A2 and B2, then fill or copy the formula down. Excel adjusts the row references automatically, so the next row calculates the difference between its own dates. If the result appears as a date rather than a number, change the result cell’s format to General or Number using the Number Format dropdown on the Home tab.

How Excel counts dates

A date that Excel recognizes is stored as a number, with each successive calendar day represented by the next serial number. Subtraction works because the difference between those serial numbers is the day count. The displayed date format—such as 1/10/2026 or 10-Jan-2026—does not change the underlying value.

This is why a formula can return the correct calculation but look wrong: the result cell may still have a date format. Change its format to General or Number to display the numeric day count. A decimal result can also be legitimate if either cell includes a time, because Excel stores time as a fraction of a day.

For reliable calculations, enter dates as real Excel dates rather than text that merely looks like a date. If a date is imported as text, Excel may not subtract it correctly, or it may treat it differently depending on regional settings. You can check a suspect cell by changing its format to General: a recognized date will display as a serial number, while text generally remains text.

When building a date from separate year, month, and day values, the DATE function avoids ambiguity from regional date formats. With the year in A2, month number in B2, and day number in C2, use =DATE(A2,B2,C2). For example, 2026 in A2, 1 in B2, and 10 in C2 produce January 10, 2026, as an Excel date value.

Use DAYS when you want an explicit date-difference function

The DAYS function returns the number of days between two dates. Its argument order is end date first, then start date, which is worth checking when you read or edit a formula. With the start date in A2 and the end date in B2, enter =DAYS(B2,A2). For January 1 through January 10, 2026, it returns 9, just like subtraction.

DAYS can make a worksheet easier to interpret because the function name says what the calculation is doing. It is useful if other people will maintain the workbook or if you prefer a named function to a basic arithmetic expression. For a straightforward two-date difference, however, it does not provide a different day-count convention from subtracting the dates.

Both approaches can return a negative number if the dates are reversed. For example, if B2 is earlier than A2, subtracting A2 from B2 gives a negative difference; DAYS with the same end-date and start-date assignments also reflects that order. Negative results can help reveal reversed dates, but they are not an error by themselves.

Choose between elapsed and inclusive counting

A frequent source of confusion is the difference between elapsed days and the number of calendar dates touched by a period. From January 1 to January 10, the elapsed difference is 9 days. If a form or report asks how many dates are included and both the start and end dates count, the answer is 10. Neither answer is universally right; the wording and purpose of the calculation decide which convention applies.

For an inclusive count, with the first date in A2 and the last date in B2, use =B2-A2+1. The January 1–10 example returns 10. Adding one counts the starting date as a date in the range. This is common when counting days of attendance, a booking that includes both arrival and departure dates, or the number of dates in a selected reporting period, though business rules may define those examples differently.

Do not add one automatically to every date difference. For calculating someone’s age in days, the time between two timestamps, or the number of days remaining until a deadline, an elapsed-day calculation is generally what the question asks for. For inclusive counts, confirm that both boundary dates should be included; some processes count the start date but exclude the end date.

When DATEDIF is useful

DATEDIF calculates the difference between two dates using a specified unit. It can return days, months, or years, so it is handy when the question is not simply “how many days?” Excel supports DATEDIF for compatibility, although the function may not appear in formula autocomplete or the usual function list.

With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"d") to return the number of days. With January 1 in A2 and January 10 in B2, the result is 9. The start date must be earlier than or equal to the end date; otherwise, DATEDIF can return a number error.

The unit argument changes the meaning of the result. The unit “m” counts complete months, while “y” counts complete years. A complete month is not the same as a fixed number of days, so use a day-difference calculation when the requested unit is days. DATEDIF is not needed just to count days, but it can be useful when a worksheet needs multiple date units or follows an existing workbook convention.

Calculate working days instead of calendar days

If weekends should be excluded, use NETWORKDAYS. It counts working days between two dates and includes both the start and end dates when they are working days. With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). Monday through Friday, January 5 through January 9, 2026, returns 5, assuming there are no holidays to exclude.

To exclude holidays too, put actual holiday dates in D2:D10. With the start and end dates in A2 and B2 and the holiday list in D2:D10, use =NETWORKDAYS(A2,B2,D2:D10). Excel ignores dates in that list that do not fall within the period. Keep the holiday cells as real dates, and consider using a clearly labeled list so another person can see which non-working dates the calculation uses.

For a weekend pattern other than Saturday and Sunday, NETWORKDAYS.INTL lets you specify the weekend. With dates in A2 and B2 and holidays in D2:D10, use =NETWORKDAYS.INTL(A2,B2,"0000011",D2:D10) for a standard Saturday–Sunday weekend. In the seven-character weekend string, positions run from Monday through Sunday, and a 1 marks a non-working day. If the organization uses another schedule, change the pattern to match it; do not assume every workplace has the same weekend.

NETWORKDAYS and NETWORKDAYS.INTL answer a different question from ordinary subtraction. They return a count of working dates under a weekend and holiday rule, rather than elapsed calendar days. They also count qualifying start and end dates, so check whether the inclusive behavior matches the requirement.

Handle times, blanks, and common errors

Excel dates can include times. If A2 is January 1 at 9:00 a.m. and B2 is January 2 at 9:00 a.m., their difference is exactly one day. If B2 is January 2 at 3:00 p.m., the difference is 1.25 days because six extra hours equal one quarter of a day. A cell formatted as General or Number may display the decimal; a cell formatted as a date may hide the result behind an unexpected display.

If the goal is to compare calendar dates while ignoring the time of day, and A2 and B2 contain date-time values, use =INT(B2)-INT(A2). INT removes the fractional time portion from each serial value before subtracting. In the example where A2 is January 1 at 9:00 a.m. and B2 is January 2 at 3:00 p.m., the result is 1 calendar-day boundary crossed. This is not the same as the number of completed 24-hour periods in every possible interval.

Blank cells can otherwise behave like zero in some calculations, which can create a misleading result. With a start date in A2 and an end date in B2, use =IF(OR(A2="",B2=""),"",B2-A2) to leave the result blank until both cells contain values. This formula is useful in a template, but it does not validate that the entries are genuine dates; validation may be needed if other people will enter data.

A number appears as a date: Change the result cell’s format to General or Number.
The formula returns an error: Check that both cells contain recognized dates and that a DATEDIF start date is not later than its end date.
The result is negative: Confirm whether the dates are intentionally in reverse order.
The result includes decimals: Check whether one or both inputs include times.
A date is treated as text: Re-enter or convert it as a real date, and check regional date-entry conventions.

In English-language Excel with typical regional settings, formulas use commas between function arguments. Depending on regional settings, Excel may display or require semicolons instead. If a copied formula is rejected, check the list separator used by your Excel installation rather than changing the date calculation itself.

Pick the formula that matches the question

Before choosing a formula, decide what “days between” means in the context of the worksheet. A basic date difference, an inclusive count, a working-day total, and a date-time duration are related but distinct calculations. Using the right method avoids off-by-one surprises and makes the result easier to explain.

Use subtraction for a simple elapsed calendar-day difference.
Use DAYS if you prefer a function whose name describes the calculation.
Use subtraction plus one when both boundary dates should count.
Use NETWORKDAYS or NETWORKDAYS.INTL when weekends and possibly holidays should be excluded.
Use DATEDIF when you need a day, month, or year unit from the same function.
Use INT on each value first when the calculation should ignore time-of-day portions.

For shared workbooks, label the input columns clearly—for example, “Start date” and “End date”—and state whether the result is elapsed days, inclusive days, or working days. A short note about the weekend pattern or holiday list is especially useful when the result feeds a schedule, service-level report, or other process where the counting rule matters.

Frequently Asked Questions

What is the simplest Excel formula for days between two dates?
With the start date in A2 and end date in B2, use =B2-A2. It returns the elapsed day difference when Excel recognizes both entries as dates.

Why does Excel show a date instead of the number of days?
The result cell is probably formatted as a date. Select it and choose General or Number from the Number Format dropdown on the Home tab to display the day count.

How do I count both the start date and end date?
With the earlier date in A2 and later date in B2, use =B2-A2+1. This adds both endpoints to the count; use it only when both dates should be included.

How do I calculate days excluding weekends?
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). Add a holiday range as the third argument if those dates should also be excluded.

Can Excel calculate days between dates that include times?
Yes. Subtracting date-time values returns a number of days that may include a decimal fraction. If you want to ignore the times and compare calendar dates only, remove each time portion before subtracting.

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.