Excel Date Difference in Days: Formulas and Examples

To find the number of days between two dates in Excel, subtract the earlier date from the later one: if the start date is in A2 and the end date is in B2, enter =B2-A2. For January 1 to January 10, 2025, the result is 9 days; add 1 if you want to count both calendar dates.

How Excel Calculates a Date Difference

Excel stores dates as serial numbers, with each successive calendar day represented by the next whole number. Subtracting one date serial from another therefore gives the elapsed number of days between them. The displayed appearance of a date, such as 1/10/2025 or Jan 10, 2025, does not change the underlying calculation.

For a basic calculation, put the starting date in A2 and the ending date in B2, then enter =B2-A2 in another cell. With A2 set to January 1, 2025, and B2 set to January 10, 2025, the result is 9. This is the number of day boundaries crossed, not a count that includes both the first and last dates.

The formula works with dates entered directly into cells, as well as dates produced by other formulas. It is often the clearest option for elapsed calendar days because the direction of subtraction is visible. Reversing the references reverses the sign: subtracting January 10 from January 1 returns -9.

Using DAYS or DATEDIF

Excel also offers functions for date intervals. With the start date in A2 and the end date in B2, enter =DAYS(B2,A2) to get the same result as subtracting the dates. DAYS takes the end date first and the start date second. The function is useful when its argument order makes a formula easier to read, but it does not calculate a different kind of day count.

DATEDIF can return the difference in specified units. With the start date in A2 and the end date in B2, enter =DATEDIF(A2,B2,"d") to return the number of complete days between them. For January 1 and January 10, 2025, it returns 9. The “d” unit means days; other unit codes can calculate completed months or years, but those are not interchangeable with a simple day count.

DATEDIF is a legacy compatibility function and may not appear in Excel’s function autocomplete or help suggestions, even though it works in current Excel for Microsoft 365. The end date must be on or after the start date; otherwise, DATEDIF can return a #NUM! error. For straightforward day differences, subtraction or DAYS is usually easier to inspect.

Deciding Whether to Count Both Dates

A frequent source of confusion is whether a date range is counted inclusively. Subtracting January 1 from January 10 gives 9 because that is the elapsed time from the start of January 1 to the start of January 10. If a schedule, event, or form needs to count every calendar date touched, January 1 through January 10 contains 10 dates.

With the start date in A2 and the end date in B2, add one to include both endpoints: =B2-A2+1. For January 1 through January 10, 2025, the result is 10. This convention is common for booking nights, reporting periods, and date ranges described as “from and including” both dates. It is not suitable for every task: a hotel stay from check-in to check-out usually counts nights, so the ordinary difference of 9 dates is often the relevant result.

Decide what the number represents before choosing a formula. “How many days elapsed?” generally means subtraction. “How many dates are included in this range?” generally means subtraction plus one, provided both endpoints are included. Write that rule into a worksheet heading or note so another person understands why the result differs from ordinary date subtraction.

Calculating Weekdays and Excluding Holidays

Calendar-day subtraction includes Saturdays, Sundays, and holidays. For a working-day count that excludes Saturday and Sunday, use NETWORKDAYS. With a start date in A2 and an end date in B2, enter =NETWORKDAYS(A2,B2). If A2 is Monday, January 6, 2025, and B2 is Friday, January 10, 2025, the result is 5 working days.

NETWORKDAYS counts both the start and end dates when they are working days, which is different from ordinary subtraction. It excludes weekends, but it does not know which public or company holidays apply unless you provide them. If the holiday date is in C2, use =NETWORKDAYS(A2,B2,C2:C2). For the January 6–10 example, if C2 contains Wednesday, January 8, 2025, the result is 4.

For a weekend pattern other than Saturday and Sunday, NETWORKDAYS.INTL lets you specify which days are weekends. With the start and end dates in A2 and B2, and a Sunday-only weekend, enter =NETWORKDAYS.INTL(A2,B2,11). The weekend argument 11 means Sunday only. The function also accepts a holiday range as a fourth argument, which is helpful when a work calendar has both unusual weekends and designated holidays. Check the function’s weekend code or seven-character weekend pattern carefully; a mistaken pattern can quietly produce a plausible but incorrect count.

When Dates Include Times

Excel date-and-time values include a fractional part for the time of day. Subtracting two such values returns a number of days that can include a fraction. If A2 contains January 1, 2025 at 9:00 AM and B2 contains January 2, 2025 at 9:00 AM, the result is 1. If B2 is January 2 at 3:00 PM instead, the elapsed time is 1.25 days, or 30 hours.

With a start date and time in A2 and an end date and time in B2, use =B2-A2 for the elapsed duration in days. To display the result as a decimal, select the result cell and choose Home > Number Format > General, or use a numeric format with decimal places. A result displayed as a date may look like an unrelated calendar day because Excel is formatting the numeric duration as a date, not because the subtraction failed.

If you need whole 24-hour periods rather than a fractional day, use the integer part of the elapsed duration. With date-and-time values in A2 and B2, enter =INT(B2-A2). For a 30-hour interval, this returns 1 complete 24-hour period. This is not the same as counting calendar dates crossed: an interval from 11:00 PM to 1:00 AM crosses midnight but lasts only two hours.

Making Results Easier to Read

For a plain day count, format the result as a number rather than a date. Select the cell and choose Home > Number Format > Number, or choose General if you do not need fixed decimal places. If the formula returns a decimal because the inputs contain times, Number format makes that fraction visible; it does not round the underlying value.

A custom label can make a report more readable, but text formatting should not replace the numeric result if you plan to calculate with it later. For instance, with a day count in C2, enter =C2&" days" to display a value such as “9 days.” This formula produces text, so other calculations should refer to C2, not to the labeled result. A number format that adds a word can preserve the value as numeric, but plain numbers are usually simpler for downstream calculations.

For a list of intervals, put start dates in A2:A20 and end dates in B2:B20. Enter =B2-A2 in C2, then fill the formula down. Excel adjusts the row references for each record. If some rows are incomplete, a simple subtraction may show an unexpected value or an error; use a blank-checking formula to leave the result empty until both dates are present. With starts in A2:A20 and ends in B2:B20, enter =IF(OR(A2="",B2=""),"",B2-A2) in C2 and fill down. This keeps unfinished rows visually clear.

Common Errors and How to Fix Them

The result looks like a date. The formula may be correct, but the result cell has a date format. Change it to General or Number.
The answer is negative. The dates may be in the wrong order. Use end date minus start date, or keep a negative result if it intentionally represents a reversed interval.
The formula returns #VALUE! or an unexpected answer. One or both inputs may be text that only looks like a date. Check the cell contents and convert valid text dates to actual Excel dates. DATEVALUE can convert text that Excel recognizes as a date, but recognition depends on the workbook’s regional date conventions.
The answer is off by one. Decide whether the question asks for elapsed days or an inclusive count of dates, then use subtraction or subtraction plus one accordingly.
A workday count is too high. NETWORKDAYS excludes weekends but will not exclude holidays unless a holiday range is supplied. Confirm the holiday cells contain real dates.

Regional settings can affect how ambiguous entries such as 03/04/2025 are interpreted: that text could mean March 4 or April 3 depending on the date convention. When importing data, use unambiguous dates or verify the parsed values before calculating. Also be cautious when moving workbooks between systems with different date-system settings. Excel for Windows normally uses the 1900 date system; the 1904 date system is available for workbook compatibility and can change how serial dates correspond if settings or data are mixed. Most everyday workbooks do not require changing this setting.

Choosing the Right Method

Use subtraction for a direct elapsed calendar-day difference. It is short, widely understood, and works naturally with dates that include times. Use DAYS when you prefer a function with explicit end-date and start-date arguments. Use DATEDIF with the “d” unit when working with a formula that already uses DATEDIF for other interval units, while remembering its date-order requirement.

Choose NETWORKDAYS or NETWORKDAYS.INTL when the question is specifically about working days rather than calendar days. Add a holiday range if holidays should not count. For inclusive ranges, add one only when both endpoints are meant to be counted. These methods answer different questions, so there is no single formula that is correct for every meaning of “days between.”

Frequently Asked Questions

Does Excel count the start date when subtracting dates?

No. Subtraction gives elapsed days between the two date values. For January 1 to January 10, the result is 9. To count both January 1 and January 10 as included dates, add 1.

What is the simplest formula for days between two dates?

With the start date in A2 and the end date in B2, use =B2-A2. Make sure the result cell is formatted as General or Number so Excel displays a day count rather than a date.

How do I calculate business days between dates?

With the start date in A2 and the end date in B2, use =NETWORKDAYS(A2,B2) to exclude Saturday and Sunday. Add a holiday range if your work calendar excludes specific holidays too.

Why does my date difference show a decimal?

One or both cells probably include a time. Excel stores time as part of a day, so 12 hours contributes 0.5. Use INT on the difference for completed 24-hour periods, or retain the decimal if you need the precise elapsed duration.

Can Excel subtract dates entered as text?

Only if Excel can interpret and convert the text as dates; text that merely resembles a date may not behave like a date serial. Convert the values to real dates, and check regional date settings when imported or ambiguous text is involved.

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.