To calculate calendar days between two dates in Excel, subtract the earlier date from the later one: with the start date in A2 and the end date in B2, enter =B2-A2. If A2 contains March 1, 2026, and B2 contains March 10, 2026, the result is 9 days; add 1 if you want to count both the start and end dates.
The basic date subtraction formula
Excel stores recognized dates as serial numbers, so ordinary subtraction gives the number of days separating them. For example, March 10 is nine date steps after March 1. In Microsoft Excel for Microsoft 365 on Windows, put the starting date in A2 and the ending date in B2, then enter =B2-A2 in another cell. With March 1, 2026, in A2 and March 10, 2026, in B2, it returns 9.
This calculation measures the difference between the dates, not the number of named dates in an inclusive range. It is useful for elapsed calendar days, such as the gap between an order date and a delivery date, the age of a task, or the number of days between two events. It counts weekends and holidays because it is based on calendar dates, not working days.
Once you have the formula, you can copy it down a column to calculate a separate difference for each row. For example, if each row has a start date in column A and an end date in column B, a formula entered in C2 can be filled into C3, C4, and the rows below. Excel adjusts the cell references for each row.
How Excel treats dates and results
Dates that Excel recognizes are stored as numbers, even though they are displayed in a human-friendly format such as 3/1/2026. Subtracting those underlying values produces a number of days. The cell containing the result may display that number as a date if it has a date format, so a result that looks unexpected may be a formatting issue rather than a faulty calculation.
To show the difference as a number, select the result cell and choose Home > Number Format > General or Home > Number Format > Number. A whole-number result is appropriate when both inputs are dates without times. Changing the display format does not change the underlying calculation; it only changes how Excel shows the value.
Excel can also store a time as a fraction of a day. For instance, noon represents half a day. If either input contains a time as well as a date, subtraction can return a decimal rather than a whole number. That distinction matters when the question is about calendar dates rather than exact elapsed time, and it is covered below.
Decide whether to count both endpoints
“Days between” most often means the difference between the two dates, which excludes the starting date from the count. From March 1 to March 10, there are nine elapsed days. A stay, event, or date range may instead require counting every date touched, including both the first and last dates. That inclusive count is ten dates in this example.
With the start date in A2 and end date in B2, use =B2-A2+1 to count both endpoints. If A2 is March 1, 2026, and B2 is March 10, 2026, the result is 10. The extra 1 changes the counting convention; it does not alter the dates themselves.
Choose the convention that matches the task. A project duration stated as elapsed time commonly uses subtraction alone. A list of calendar dates covered by a booking or a period may need inclusive counting. If the dates are identical, subtraction returns 0, while the inclusive formula returns 1 because that range contains one date. Making this choice explicit prevents off-by-one errors.
Using DATEDIF as an alternative
Excel also supports DATEDIF for measuring the interval between two dates in selected units. With the start date in A2 and the end date in B2, enter =DATEDIF(A2,B2,"d"). For March 1, 2026, in A2 and March 10, 2026, in B2, it returns 9 days.
The “d” unit asks for the number of days in the interval. For the straightforward task of finding calendar days, subtraction is usually clearer and easier to inspect. DATEDIF can be useful when a formula already uses it to calculate completed years or months alongside days, but it is not necessary for a simple day difference. It also expects the start date to be earlier than or equal to the end date; reversed inputs can produce an error.
DATEDIF is a compatibility function retained in Excel, but it may not appear in the function autocomplete or provide the same on-screen guidance as many newer functions. That does not make it the preferred choice for this calculation. Use whichever method best communicates the intended calculation to the people who will maintain the workbook.
When the cells include times
If the cells contain timestamps, direct subtraction measures elapsed time in days and may produce a decimal. For example, the interval from March 1 at 3:00 p.m. to March 10 at 9:00 a.m. is less than nine full 24-hour periods. But the calendar dates March 1 and March 10 are nine days apart. Those are different questions, and the formula should match the one being asked.
With a date and time in A2 and another date and time in B2, use =INT(B2)-INT(A2) when you want the difference between their calendar dates and want to ignore the time portions. If A2 is March 1, 2026, at 3:00 p.m., and B2 is March 10, 2026, at 9:00 a.m., the result is 9. INT removes the fractional time portion from each serial value before subtracting.
If you need the exact elapsed duration instead, keep the times and subtract the full values. Format the result as a number to see a fraction of a day, or multiply by 24 to express the duration in hours. Do not remove times when the time of day matters—for example, when measuring the time between appointments or checking whether a response met an hourly deadline.
Calendar days versus working days
Simple subtraction counts every calendar day, including Saturdays, Sundays, and holidays. That is correct for a date span when every day matters, but it is not a business-day count. A work schedule often needs a different function, such as NETWORKDAYS, which counts working days between dates and can exclude specified holidays.
For example, a five-day calendar interval can contain fewer than five working days if it crosses a weekend. Conversely, a calendar-day formula will still return the full date difference. Before changing formulas, identify whether the requirement says calendar days, weekdays, business days, or working days excluding holidays; these terms are not interchangeable.
NETWORKDAYS uses inclusive endpoint counting for qualifying workdays, so its result should not be interpreted as the same measure as subtracting dates. If you are comparing results or building a report, document which convention is used. Holiday lists also need to be maintained as real Excel dates if they are supplied to a workday calculation.
Common problems and how to fix them
- The result displays as a date. Change the result cell to General or Number using Home > Number Format. The subtraction may already be correct; Excel is simply displaying its numeric result with a date format.
- The result is negative. The end date is earlier than the start date. Check whether the dates were entered in the intended cells. If negative durations are meaningful in your analysis, decide how you want to present them rather than silently reversing the inputs.
- The result is a decimal. One or both input cells probably contain times. Use INT on both values when you specifically want calendar-date difference, or keep the decimal when you need elapsed time.
- The formula returns an error or an implausible number. A date may have been imported as text rather than stored as an Excel date. Check whether the cell can be displayed as a date using a date number format, and review the source data and regional date order. A string such as 04/05/2026 can be ambiguous between month/day and day/month conventions.
- The answer is one day off. Confirm whether the task requires elapsed difference or inclusive counting. Also check for hidden times, which may affect a calculation even if the cell display shows only the date.
For a table where dates are entered gradually, you may prefer the result to stay blank until both cells are filled. With the start date in A2 and end date in B2, use =IF(OR(A2="",B2=""),"",B2-A2). If either cell is blank, it returns an empty string; if both contain dates, it returns their difference. This avoids displaying a misleading number for an unfinished row. Ensure that cells containing the dates are actual dates, not text that merely looks like a date.
Frequently Asked Questions
Does Excel count the start date?
Not when you subtract the dates. A2-to-B2 subtraction returns the number of date steps between them, so March 1 to March 10 is 9. Add 1 when your definition requires counting both the first and last calendar dates.
What formula counts days including weekends?
Use date subtraction, such as =B2-A2 when the start date is in A2 and the end date is in B2. It counts all calendar days, including weekends and holidays. Use a working-day function only if you want to exclude specified days.
Can Excel calculate the days between dates in different years?
Yes. Subtraction works across months, years, and leap days as long as both cells contain recognized Excel dates. For example, the formula uses the actual date serials, so you do not need to add a special adjustment for February 29.
Why does subtracting two dates show a decimal?
The cells likely include times. Excel stores time as a fraction of a day, so subtracting date-time values can return a fraction. Use INT on each value before subtraction if you want calendar-date difference, or keep the decimal if elapsed hours matter.
Should I use DATEDIF or subtraction?
For a plain calendar-day difference, subtraction is direct and easy to understand. DATEDIF with the “d” unit also returns the day interval, and may be convenient in a formula that calculates other interval units. In either case, verify the endpoint convention and date order.
Related
- Excel Date Number to Text: Convert Dates Clearly
- Excel Data Validation List: Create a Yes/No Dropdown
- Excel Data Validation Simple List: Create and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter an Excel Sheet by DateTo filter an Excel sheet by date in Microsoft Excel for Microsoft 365 on Windows, select a cell…
- NPV vs PV in Excel: Formulas and Key Differences ExplainedIn Excel, use NPV to discount a series of periodic cash flows, but keep a time-zero investment outside…
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- Excel UPPER Function: Convert Text to UppercaseIn Excel, use the UPPER function to convert text to uppercase: with the text to change in cell…
- Sort List in Excel Formula: SORT, SORTBY, and MoreTo sort a list with a formula in Microsoft Excel for Microsoft 365, use SORT: with values in…
- RANK Excel Function: Examples, Ties, and Sort OrderTo rank a value in Excel, use RANK.EQ(number,ref,[order]): for example, with scores in B2:B6, =RANK.EQ(B2,$B$2:$B$6,0) returns B2’s position…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.