To find the number of 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, 2024 returns 9 days; use DATEDIF or a workday function when you need whole months, years, or business days instead.
How Excel calculates a date difference
Excel stores dates as serial numbers, so subtracting one date from another calculates the number of elapsed days. The formula does not need a special date-difference operator: the minus sign is enough. The result is a number, not a new date, although Excel may display it with a date format if the result cell is formatted that way.
For the basic example, enter January 1, 2024 in A2 and January 10, 2024 in B2. With those dates in place, the formula is =B2-A2, and the result is 9. The start date itself is not counted as a completed day. If you want to count both the first and last calendar dates—for example, a nine-day span that touches ten dates—use an inclusive count. With the same A2 and B2 layout, enter =B2-A2+1; it returns 10.
After entering a subtraction formula, set the result cell to General or Number if Excel shows an unexpected date. On Windows, select the cell and choose Home > Number Format, then choose General or Number. The Mac ribbon offers the equivalent number-format choices on its Home tab. Applying a number format changes the display, not the calculation.
Choosing the right formula for your question
“Date difference” can mean several things. Decide what you want the result to represent before choosing a formula:
- Elapsed days: subtract the dates. This gives the actual number of days between them.
- Inclusive calendar days: subtract and add 1 when both endpoint dates should count.
- Complete months or years: use DATEDIF with the appropriate unit.
- Working days: use NETWORKDAYS, optionally excluding a holiday list.
- Approximate years or a decimal year fraction: consider YEARFRAC and choose its day-count basis deliberately.
These answers are not interchangeable. A 30-day span is not always one calendar month, and a difference of 365 days does not always mean one whole year of age. Pick the measure that matches the question, such as project duration, employee tenure, age, or days remaining until a deadline.
Calculate days, including negative and inclusive results
Basic subtraction works for dates in either order, but the sign matters. A positive result means the date in B2 is later than the date in A2; a negative result means it is earlier. That can be useful for identifying overdue or reversed records. If you only need the size of the gap regardless of direction, use the absolute value. With the earlier or later date in either A2 or B2, enter =ABS(B2-A2); dates ten days apart return 10 in either order.
For a deadline countdown, the order of subtraction can make the sign easier to interpret. With today’s date in A2 and a deadline in B2, =B2-A2 returns the number of days remaining when the deadline is in the future, zero when the dates match, or a negative number when it has passed. To have Excel update the current date automatically, put =TODAY() in A2; this formula returns the current date according to the computer’s date. Recalculation settings can affect when volatile functions such as TODAY update.
If negative results usually indicate data entry mistakes, make the issue visible rather than silently taking the absolute value. With a start date in A2 and an end date in B2, use =IF(B2<A2,"Check dates",B2-A2). This displays a warning for a reversed date range and otherwise returns the elapsed days. The result column will contain both text and numbers, so use it for review rather than for calculations that require a numeric result in every row.
Find complete months and years with DATEDIF
DATEDIF returns the number of complete units between two dates. It is an older compatibility function that remains available in Excel, although it may not appear in the formula autocomplete list. Its start date must be earlier than or equal to its end date. The unit is supplied as text: “d” for days, “m” for complete months, and “y” for complete years.
For example, with January 15, 2024 in A2 and March 14, 2024 in B2, enter =DATEDIF(A2,B2,"m"). The result is 1 complete month, not 2: the second monthly anniversary, March 15, has not arrived. With January 15 in A2 and March 15 in B2, the same formula returns 2. This is why DATEDIF’s month result may differ from a casual count of calendar-month names touched by a date range.
For a whole-year age or tenure calculation, enter the birth or start date in A2 and the later date in B2. The formula =DATEDIF(A2,B2,"y") returns the number of completed years. Someone born on June 15, 2000 is 23 on June 14, 2024, and 24 on June 15, 2024. For additional components, “ym” returns remaining months after complete years, while “yd” returns remaining days after complete years. These are component values, not a replacement for the total elapsed days. Be cautious with “md”; it can produce surprising results around month ends, so it is generally better to use subtraction or a clearly defined date rule for day counts.
Count working days and exclude holidays
NETWORKDAYS counts weekdays between two dates and includes both endpoints when they fall on working days. It treats Saturday and Sunday as the weekend. With January 1, 2024 in A2 and January 5, 2024 in B2, enter =NETWORKDAYS(A2,B2). The result is 5 because those dates run Monday through Friday.
To exclude holidays, list the holiday dates in D2:D3 and use =NETWORKDAYS(A2,B2,D2:D3). For instance, if D2 contains January 1 and D3 contains January 2, the same January 1–5 range returns 3 working days. The holiday cells need to contain real Excel dates, not text that only looks like dates. NETWORKDAYS includes the start and end dates where applicable, so a one-day Monday range returns 1, not 0.
If the weekend is not Saturday and Sunday, use NETWORKDAYS.INTL, which lets you specify weekend days. Its optional weekend argument accepts documented numeric patterns or a seven-character string in which the week starts Monday and weekend days are marked with 1. Because weekend arrangements vary by workplace and region, check the function’s argument help and choose a pattern that matches the schedule. This is especially important for shift or regional calendars; a simple subtraction may be more suitable if the question is elapsed calendar days rather than scheduled workdays.
Handle times, dates stored as text, and formatting
Excel stores a time as a fraction of a day. If A2 contains 9:00 AM and B2 contains 5:00 PM on the same date, subtracting the values gives one-third of a day. Format the result as a time duration or multiply by 24 to display elapsed hours. With start and end times on the same day in A2 and B2, enter =(B2-A2)*24; 9:00 AM to 5:00 PM returns 8 hours.
For a shift that crosses midnight, time-only values can make direct subtraction negative. With a start time of 10:00 PM in A2 and an end time of 6:00 AM in B2, use =MOD(B2-A2,1)*24; the result is 8 hours. This assumes the interval is less than 24 hours and that the cells contain times only. If cells contain complete date-and-time values, subtract them directly and multiply by 24 for hours; that preserves the actual number of days as well.
A date that is stored as text may not subtract correctly. If Excel does not recognize an entry as a date, check the cell’s value and regional date settings. Entries such as 03/04/2024 can mean different dates under different regional conventions. For an unambiguous formula-created date, with no cell inputs required, =DATE(2024,1,10)-DATE(2024,1,1) returns 9. DATE constructs a true Excel date from year, month, and day numbers. Changing a cell’s appearance to a date format does not convert arbitrary text into a date value.
DATEDIF versus subtraction and YEARFRAC
Use subtraction when you need an exact number of elapsed days. It is transparent, easy to audit, and appropriate for durations and countdowns. Use DATEDIF when the requirement is a count of complete months or years, such as completed service years. Its result is an integer and follows anniversary boundaries, so it should not be treated as a decimal age.
YEARFRAC returns a fraction of a year, which can be useful for certain elapsed-year calculations. Its third argument controls the day-count basis, and different bases can produce different results. For a simple example, with January 1, 2024 in A2 and January 1, 2025 in B2, =YEARFRAC(A2,B2,1) returns 1 using the actual/actual basis. For other date ranges, do not assume that every basis gives the same value or that the result matches a particular organization’s rules for service, contracts, or billing. Confirm the required convention before using it.
Another important distinction is calendar days versus working days. Subtraction counts weekends and holidays; NETWORKDAYS excludes the standard weekend and any holidays you supply. For example, a five-day Monday-to-Friday period has five elapsed days from Monday to Saturday’s date boundary, but its inclusive workday count can be five. The correct function depends on whether your question is “how much time passed?” or “how many scheduled workdays are included?”
Common problems and practical checks
If a result looks wrong, check the input values before changing the formula. Verify that both cells contain dates Excel recognizes, that the start and end references are in the intended order, and that the result cell is formatted as a number. Also check whether the request calls for an inclusive count, a complete-month count, or weekdays; each choice changes the answer in a predictable way.
- Unexpected date display: change the result format to General or Number.
- Negative number: confirm that the end date is later, or use a warning formula if reversed entries need review.
- Off by one: decide whether to count both endpoint dates and add 1 only when appropriate.
- Month result seems low: DATEDIF with “m” counts completed months, not month names touched.
- Formula error or inconsistent dates: confirm the cells are real dates and check regional date interpretation.
- Workday total differs from expectation: review endpoint inclusion, weekend pattern, and holiday range.
When a workbook moves between systems, be aware that Excel supports different date systems in some workbooks. The 1900 and 1904 date systems represent the same calendar dates with serial numbers 1,462 days apart. If copied dates between workbooks appear shifted by that amount, check the workbook’s date-system setting rather than adding an arbitrary correction to each formula.
Frequently Asked Questions
What is the simplest Excel formula to subtract two dates?
Put the start date in A2 and the end date in B2, then enter =B2-A2. The result is elapsed days. Format the result as General or Number if it appears as a date.
Does Excel have a DATEDIF function?
Yes. DATEDIF is available for compatibility, even though Excel may not show it in autocomplete. It can return days, complete months, or complete years depending on its unit argument; the start date must not be later than the end date.
How do I calculate someone’s age in Excel?
With a birth date in A2 and the date to calculate age on in B2, =DATEDIF(A2,B2,"y") returns completed years. For an age that updates daily, B2 can contain =TODAY(). Make sure the birth date is not after the calculation date.
How can I count both the start and end dates?
With the start date in A2 and end date in B2, use =B2-A2+1 for an inclusive calendar-day count. Do not add 1 automatically to every date difference; elapsed duration and inclusive date counts answer different questions.
How do I calculate the number of weekdays between dates?
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). To exclude holidays, supply a range of holiday dates as the third argument. The standard function treats Saturday and Sunday as weekends.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Null Values: Blanks, Formulas, and Power QueryMicrosoft Excel does not have a special worksheet value called NULL in the way a database does. What…
- Power Query Group By: Concatenate Text by GroupYes. Power Query’s Group By dialog does not offer a direct “concatenate text” operation, but you can group…
- SUMIF Excel Formula: Examples, Syntax, and Common FixesTo add values that match one condition, use SUMIF: with regions in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"East",B2:B6)…
- Count Unique Strings in Excel: Formulas and ExamplesTo count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use…
- Sort Excel Alphabetically: Rows, Columns, and ListsTo sort a list alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column,…
- Excel DATEDIF Formula Examples for Age and Date GapsExcel’s DATEDIF function calculates the number of complete years, months, or days between two dates: use =DATEDIF(A2,B2,"Y") for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.