To get the number of days between a start date in A2 and an end date in B2, enter =DATEDIF(A2,B2,"d"). For example, January 1 to January 10 returns 9 days: DATEDIF counts the elapsed difference, not both calendar dates as separate days.
How DATEDIF counts days
DATEDIF is a date-difference function in Excel. Its syntax is DATEDIF(start_date,end_date,unit): the first argument is the earlier date, the second is the later date, and the unit tells Excel what to return. For a day count, the unit is the text value “d”. The function returns a number, so you can use the result in calculations, summaries, or reports.
Excel stores valid dates as serial numbers, which makes a day difference possible by subtracting one date from another. DATEDIF’s “d” unit returns the total number of days separating the two dates, including days in leap years. It does not count the starting date as an extra day. Thus, the difference between May 4 and May 5 is 1, even though the dates touch two rows on a calendar.
DATEDIF is a legacy function retained for compatibility with older spreadsheet programs. It works in Microsoft Excel for Microsoft 365 on Windows, even though it may not appear in Excel’s function autocomplete or be listed alongside newer functions in the Insert Function interface. You can type it directly into a cell. The fact that it is less visible does not make it a different kind of date calculation.
Enter the formula with correctly ordered dates
Put the start date in A2 and the end date in B2, then enter this formula in C2: =DATEDIF(A2,B2,"d"). If A2 contains January 1, 2025, and B2 contains January 10, 2025, the result is 9. The arguments must be in chronological order: DATEDIF expects the end date to be on or after the start date.
For a column of records, place each start date in column A and its matching end date in column B. Enter the formula in C2, then use the fill handle—the small square at the lower-right corner of the selected cell—to copy it down. Excel adjusts the references for each row, so the next row uses A3 and B3. On Windows, you can also select the formula cell and the cells below it, then press Ctrl+D to fill down. On Mac, the equivalent shortcut is Command+D.
Make sure the cells contain actual Excel dates rather than text that only looks like a date. A date typed in an unfamiliar regional format may be interpreted differently or stored as text. To create an unambiguous test date directly in a formula, with no cell references required, use =DATEDIF(DATE(2025,1,1),DATE(2025,1,10),"d"); this returns 9. The DATE function constructs a date from year, month, and day numbers.
Elapsed days versus inclusive day counts
The common point of confusion is whether “days between” means elapsed days or the number of dates included in a period. DATEDIF returns elapsed days. From January 1 through January 10, there are 9 elapsed 24-hour calendar intervals, but 10 dates if you intend to include both January 1 and January 10 in a count.
If your rule is to count both endpoints, with the start in A2 and the end in B2, add one to the DATEDIF result: =DATEDIF(A2,B2,"d")+1. With January 1 and January 10, this returns 10. This inclusive version is useful for tasks such as counting scheduled calendar dates when the first and last dates are both part of the period. It is not automatically the right choice for every situation; use the counting rule required by your report or process.
For a same-day start and end, DATEDIF returns 0 because no full date boundary separates the two values. The inclusive formula returns 1, because the one date is included. This is a quick way to check whether an inclusive adjustment makes sense for your data. For instance, if a same-day booking should count as one day, an inclusive count may fit; if you are measuring elapsed time, zero is the meaningful result.
DATEDIF compared with simple subtraction
For ordinary date-only values, subtracting the start date from the end date gives the same day difference as DATEDIF. With the start date in A2 and end date in B2, the formula =B2-A2 returns 9 for January 1 to January 10, 2025. Since Excel dates are stored as numbers, the subtraction is direct and does not need a unit argument.
Subtraction is often the clearest choice when you want a signed result, because it can show a negative number if the dates are reversed. DATEDIF instead returns a #NUM! error when its end date is earlier than its start date. Subtraction also makes it easy to see the basic arithmetic. Choose DATEDIF if its named date-difference structure is useful or if you are extending a formula that already uses its units; choose subtraction for a straightforward difference or when negative results matter.
One distinction is display format. The result of subtraction is numeric, but Excel may format the destination cell as a date if that cell previously had date formatting. If the result appears as another date instead of a day count, select the result cell and choose Home > Number Format, then select General or Number. DATEDIF also returns a number, so formatting the output as General or Number is sensible for either approach.
Dates that include times
Excel date-time values can include a fractional part for the time of day. Subtracting two date-time values returns elapsed time measured in days, including a fraction: for example, 12 hours is 0.5. That can be useful for calculating exact durations, but it may not be what you want when the question is simply how many calendar dates have passed.
If A2 and B2 contain date-time values and you want elapsed days including partial days, use =B2-A2 and format the result as Number. If you want the difference between the calendar dates only, ignoring the times, with the same cell layout, use =INT(B2)-INT(A2). INT removes the fractional time portion from each serial value before subtraction. For example, a start at 11:00 p.m. and an end at 1:00 a.m. the next date gives 0.0833 elapsed days by subtraction, but a calendar-date difference of 1 using the INT formula.
DATEDIF is intended for date units, not for reporting hours or partial days. If time components could affect the answer, decide whether the desired measure is calendar dates, complete days, or elapsed hours before choosing a formula. Formatting a fractional result to display zero decimal places only changes what is shown; it does not remove the underlying fraction.
Common errors and practical checks
The most frequent DATEDIF error is #NUM!, usually because the end date is earlier than the start date. Check the cell references and the dates in that row. If you want a signed result, subtraction is more suitable. If the dates may appear in either order but you only need a nonnegative span, use the absolute difference. With dates in A2 and B2, =ABS(B2-A2) returns 9 for January 1 and January 10 regardless of which date comes first.
A #VALUE! error can point to a value Excel cannot interpret as a date. Imported data is a common source: a cell may contain date-looking text rather than a serial date. Check the cell by changing its number format; a real date will typically display as a number when formatted as General, while text will remain text. Converting text may require importing the source with the correct locale or using Excel’s data-conversion tools, depending on how the values are structured.
Blank cells deserve attention too. A formula copied down a long table can produce misleading results when one of its date cells is empty, because Excel may treat a blank as zero in calculations. If A2 is the start date and B2 is the end date, a guarded formula that leaves the result blank until both are filled is =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"d")). This avoids displaying a number for an incomplete row, but it still assumes the dates are in the correct order; an end date before the start date will still produce #NUM!.
Also check that each row pairs the right start date with the right end date. A correct formula cannot detect mismatched records, such as a customer’s start date paired with another customer’s end date. When results look surprising, inspect the source values, confirm their order, and compare one result manually using a calendar or direct subtraction.
Choosing a method for your task
Before settling on a formula, decide what the result is meant to represent. These checks help prevent a technically valid formula from answering the wrong question:
- Elapsed date difference: Use DATEDIF with the “d” unit or subtract the earlier date from the later date. Both return 9 for January 1 through January 10.
- Inclusive count: Add one when both endpoints count as included dates. Confirm this rule with the process owner or reporting definition.
- Signed difference: Use end date minus start date if a negative number should reveal reversed dates.
- Order-independent span: Use ABS around a subtraction if only the nonnegative distance matters.
- Date-time duration: Subtract the full date-time values for elapsed days, or convert the result to hours by multiplying by 24 if a decimal hour count is wanted.
These choices are about definitions, not just Excel syntax. For example, a service-period report might count every included date, while a delay measure might need a signed difference to identify early versus late completion. Writing down the intended rule first makes it easier to validate the formula and explain the number to someone reviewing the sheet.
Frequently Asked Questions
Does DATEDIF count the start date?
No. DATEDIF with the “d” unit returns the difference between the dates, so identical dates return 0. Add 1 only when your specific count includes both the start and end dates.
Why does DATEDIF return #NUM!?
Most often, the end date supplied to DATEDIF is earlier than the start date. Check the argument order and the row’s values. Use subtraction if you want a negative result, or ABS with subtraction if you need an order-independent positive span.
Is DATEDIF available in Microsoft 365 Excel?
Yes. DATEDIF works in Excel for Microsoft 365 on Windows, although it may not appear in autocomplete. Type the function and its arguments directly into the formula bar or a cell.
Does DATEDIF include weekends?
Yes. The “d” unit counts calendar days, including Saturdays and Sundays. It does not calculate working days. A business-day calculation is a different task and requires a working-day function and, where relevant, a list of holidays.
Should I use DATEDIF or subtract dates?
For date-only values, both methods give the same ordinary day difference. DATEDIF explicitly names a date-difference unit; subtraction is simpler and can return negative values when the dates are reversed. Pick the method that matches the required result and handle inclusive counting separately.
Related
- Excel Column K Number: What It Means and How to Find It
- ICD-10 Z Codes List in Excel: Find, Import, and Check
- Excel Color Scales: How Conditional Formatting Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIF After a Date: Formula and ExamplesTo sum values for dates strictly after a cutoff in Excel, use =SUMIF(A2:A100,">"&DATE(2024,1,1),B2:B100), where column A contains dates…
- Excel INDEX Errors: Common Causes, Meanings, and FixesAn Excel INDEX error usually means the requested row or column falls outside the range (#REF!), an index…
- Conditional Formatting If a Cell Is Blank in ExcelTo highlight blank cells in Excel for Microsoft 365 on Windows, select the cells, choose Home > Conditional…
- Excel COUNTIF for Red Text: Methods That Actually WorkCOUNTIF cannot directly count cells by red font color: it evaluates cell values, not formatting. To count red…
- ISERROR Function in Excel: What It Does and How to Use ItIn Excel, ISERROR checks whether a value or formula result is any error value, and returns TRUE if…
- PMT Function in SQL: Calculate Loan Payments by PeriodSQL does not have a universally available built-in PMT function. To calculate a fixed loan payment, use the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.