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. Excel returns the elapsed days; use a function such as DATEDIF or NETWORKDAYS when you need completed months, years, or working days instead.
The quickest way to subtract dates
Enter the dates in two cells, then subtract the start date from the end date. With the start date in A2 and the end date in B2, enter =B2-A2. For example, if A2 contains January 1, 2025, and B2 contains January 10, 2025, the result is 9 days. This measures the time between the dates, not a count that includes both the first and last day.
Excel dates are stored as serial numbers, so ordinary subtraction works. If the result appears as another date rather than a number of days, the calculation may still be correct: the result cell is probably formatted as a date. Select it, then choose Home > Number Format > General or Number. You can also use the keyboard shortcut Ctrl+Shift+~ on Windows to apply General format. On Mac, use Control+Shift+~ where supported, or choose the format from the Home tab.
For a formula that explicitly describes the operation, with the start date in A2 and the end date in B2, enter =DAYS(B2,A2). DAYS returns the number of days between its end-date and start-date arguments. It produces the same result as subtraction for ordinary date values, so subtraction is usually the simplest choice.
How Excel stores dates
Excel represents dates as serial numbers: the whole-number portion counts days from a starting point in the workbook’s date system, and any decimal portion represents a time of day. This is why subtracting two dates gives a number of days and subtracting two date-and-time values can give a fraction of a day. The numbers may not be visible while cells use a date display format, but they are what Excel calculates with.
Most Windows workbooks use the 1900 date system, while some Mac workbooks may use the 1904 system. The same date has a different underlying serial number in those systems, so moving raw date values between workbooks with different date systems can shift displayed dates. Excel generally handles dates entered or copied as dates, but the distinction matters when transferring values between older or specially configured workbooks. Check the workbook’s date-system setting under File > Options > Advanced on Windows; the setting is under Excel > Settings on Mac, though its exact placement can vary by release.
Dates also depend on regional settings. A value such as 03/04/2025 can mean March 4 or April 3 depending on the date convention in use. If Excel treats an entry as text, subtraction may return an error or an unexpected result. When sharing a workbook, unambiguous entries such as 4-Mar-2025, or dates created with Excel’s date picker or DATE function, help avoid confusion.
Choose a calculation that matches the question
“Difference between dates” can mean several things. Decide what the result should count before choosing a formula:
- Elapsed days: subtract the start date from the end date. This is useful for durations, delivery intervals, and age expressed in days.
- Calendar days including both endpoints: add one to the elapsed-day result when both the start and end dates should count as days in a period.
- Completed months or years: use DATEDIF, which counts whole completed units between two dates.
- Working days: use NETWORKDAYS or NETWORKDAYS.INTL to exclude weekends and, optionally, listed holidays.
- Calendar-year difference: subtract the year numbers if the question is how many year boundaries the dates cross, not how many full years have elapsed.
For example, a period from January 1 through January 10 is 9 elapsed days, but it covers 10 calendar dates if both endpoints are included. That distinction affects leave records, event schedules, and some billing or reporting rules. Excel cannot determine which convention your task requires; the formula must reflect the rule you intend to apply.
Calculate completed months and years
DATEDIF calculates the number of complete years, months, or days between two dates. It remains available in Microsoft 365 for compatibility with older workbooks, although Excel does not display it in the formula autocomplete list. Its start date must be earlier than or equal to its end date, and the unit is supplied as text.
With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"Y") to return completed years. For January 15, 2020, through January 14, 2025, the result is 4 because a fifth full year has not yet elapsed. With the same cell layout, enter =DATEDIF(A2,B2,"M") to return completed months. The units Y and M count complete years and complete months, respectively.
To find the number of months left after counting complete years, with the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"YM"). This returns the month remainder after full years are removed. For example, a span of 2 years and 3 completed months returns 3. DATEDIF’s MD unit is less dependable for some end-of-month cases, so avoid relying on it for precise day remainders without checking the result against the dates and rules in your workbook.
Subtracting year numbers answers a different question. With the earlier date in A2 and the later date in B2, enter =YEAR(B2)-YEAR(A2) to count calendar-year boundaries. December 31, 2024, to January 1, 2025, returns 1, even though only one day elapsed. Use DATEDIF with Y when you need completed years, such as age on a particular date; use the YEAR difference only when calendar years are what you mean.
Count working days and exclude holidays
NETWORKDAYS counts weekdays between two dates, including both the start and end date when they fall on working days. It excludes Saturday and Sunday by default. With the start date in A2 and the end date in B2, enter =NETWORKDAYS(A2,B2). For Monday, January 6, 2025, through Friday, January 10, 2025, it returns 5, whereas simple subtraction returns 4 elapsed days.
To exclude holidays as well, list the holiday dates in H2:H10. With the start date in A2, the end date in B2, and holiday dates in H2:H10, enter =NETWORKDAYS(A2,B2,H2:H10). Make sure the holiday cells contain actual Excel dates, not text that merely looks like a date. The function excludes listed dates that fall on weekdays in the period; a holiday on a weekend does not reduce the weekday count again.
For a weekend other than Saturday and Sunday, use NETWORKDAYS.INTL. With the start date in A2, the end date in B2, and holiday dates in H2:H10, enter =NETWORKDAYS.INTL(A2,B2,1,H2:H10). The weekend argument 1 specifies Saturday and Sunday, matching the default; other supported weekend codes or a seven-character weekend pattern can define different nonworking days. Check the function’s weekend-code guidance if your schedule uses a less common workweek.
What if the dates include times?
When cells contain both a date and a time, subtraction returns elapsed days as a decimal. With a start date and time in A2 and an end date and time in B2, enter =B2-A2. A result of 1.5 means one and a half days, or 36 hours. Format the result as Number to see the decimal, or multiply the difference by 24 when you want hours.
For date-and-time values where only the number of whole calendar days matters, discard the time fractions before subtracting. With the start date and time in A2 and the end date and time in B2, enter =INT(B2)-INT(A2). For example, a start at 11 p.m. and an end at 1 a.m. the following day gives 1 calendar-day boundary, even though only two hours passed. Use this approach only when calendar dates, rather than elapsed hours, are the intended measure.
NETWORKDAYS is designed to count dates rather than hours in a shift. If your requirement is working hours across partial days, weekends, breaks, and holidays, the calculation needs more detail than a basic date-difference formula. Define the work schedule and time boundaries first rather than interpreting a weekday count as a count of paid or scheduled hours.
Common errors and how to fix them
A negative answer usually means the end date is earlier than the start date, or the dates have been put in the opposite order. If the dates should always be chronological, correct the entries. If the order is variable and you only want the absolute distance, with the two dates in A2 and B2, enter =ABS(B2-A2). This removes the sign, so it will not warn you that the dates are reversed.
A #VALUE! error often indicates that one or both entries are text rather than Excel date values. Check whether the cell is left-aligned unexpectedly, try changing the date format, and re-enter the value using a date convention Excel recognizes. Formatting text as a date does not necessarily convert it into a real date. If data comes from an import, use a suitable conversion process or rebuild the date from its year, month, and day components.
A #### display usually means the column is too narrow to show the formatted value, or a negative date/time result cannot be displayed with the current date system and format. Widen the column first. If a negative result is expected, show it as a number or review the date order; changing the display does not change the underlying calculation.
For a formula that flags reversed dates instead of silently returning a negative duration, with the start date in A2 and end date in B2, enter =IF(B2<A2,"Check dates",B2-A2). This returns a message when the end date precedes the start date and otherwise returns the elapsed-day count. It is useful in a data-entry sheet, but it does not validate whether either entry is a real date, so input checks may still be needed.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
Put the earlier date in A2 and the later date in B2, then enter =B2-A2. The result is elapsed days. Format the result as General or Number if it displays as a date.
How do I count both the start date and end date?
Subtract the start date from the end date and add one. With the start date in A2 and end date in B2, enter =B2-A2+1. For January 1 through January 10, this returns 10 dates rather than 9 elapsed days.
How can I calculate age in completed years?
With the birth date in A2 and the date to calculate age on in B2, enter =DATEDIF(A2,B2,"Y"). This counts full years, so the result changes on the person’s birthday, not simply at the start of each calendar year.
Why does Excel show a date instead of the number of days?
The result cell is probably formatted as a date. Select the cell and choose Home > Number Format > General or Number. This changes how the result is displayed, not the subtraction itself.
Does DATEDIF include the end date?
DATEDIF counts completed units between its start and end dates; it does not simply count every date inclusively. For a complete-month or complete-year calculation, use its M or Y unit. If you need an inclusive count of calendar dates, use subtraction plus one instead.
Can Excel calculate business days between dates?
Yes. NETWORKDAYS excludes Saturday and Sunday and can also exclude a range of holiday dates. Use NETWORKDAYS.INTL when weekends follow a different pattern, and confirm that the dates and holiday list are stored as real Excel dates.
Related
- Comparison Plot in Excel With R²: Steps and Meaning
- Company Invoice Template in Excel: Setup and Tips
- Compare Lists in Excel: Find Matches and Differences
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Create a Unique Signature in OutlookTo create a unique signature in Outlook, open the signature editor for your version, write a concise sign-off…
- Excel CONCATENATE Formula With a Space: Clear ExamplesTo join two Excel cells with a space between them, use the ampersand operator and put a space…
- Excel Formula for Years of Service From Hire DateTo calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date…
- SUMPRODUCT IF Function: How to Sum with ConditionsNo, Excel does not have a separate SUMPRODUCTIF or “SUMPRODUCT IF” function. To sum values that meet conditions,…
- VLOOKUP and HLOOKUP in Excel: Formulas and UsesIn Excel, VLOOKUP searches down the first column of a table and returns a value from a column…
- Excel Sum Unique Values: Formula Rules and ExamplesTo add each different numeric value once in Excel, put the values in B2:B8 and enter =SUM(UNIQUE(B2:B8)). The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.