To subtract one date from another in Excel, subtract the earlier date cell from the later one: if the start date is in A2 and the end date is in B2, enter =B2-A2. Excel returns the number of elapsed days; format the result as a number if it appears as another date.
How Excel stores dates
Excel dates are stored as serial numbers, with each whole number representing a day. Time is stored as a fraction of a day: noon is one half, for example. This is why ordinary subtraction works for dates and times: Excel subtracts the underlying numbers, then displays the result using the cell’s number format.
In Excel for Microsoft 365 on Windows, most workbooks use the 1900 date system. Excel also supports a 1904 date system, historically used by some Mac workbooks. The two systems assign different serial numbers to the same modern date. Excel usually adjusts dates when copying between workbooks, but linked data or imported serial numbers can expose a mismatch. If dates appear shifted by roughly four years, check the workbook’s date-system setting under File > Options > Advanced. On Mac, look under Excel > Preferences > Calculation. Avoid changing the setting casually in a workbook with existing dates, because it can change how those serial values display.
Excel’s 1900 system also preserves a historical compatibility quirk: it treats 1900 as if it were a leap year, although it was not. That affects calculations involving very old dates around February and March 1900, but is not usually relevant to contemporary schedules.
Subtracting two dates for elapsed days
Enter dates as real Excel dates, not text that merely looks like a date. When both cells contain valid dates, subtraction gives the number of days between them. The order matters: later date minus earlier date gives a positive number; reversing the order gives a negative number.
With a start date in A2 and an end date in B2, use =B2-A2. If A2 is 1 January 2025 and B2 is 10 January 2025, the result is 9 days. This measures elapsed time between the two date points, not the number of calendar dates touched. For a list, put the formula in C2 and fill it down to calculate each row’s interval.
A result displayed as a date does not necessarily mean the calculation failed. Excel may have carried the date formatting from a cell. Select the result, then choose Home > Number Format and select General or Number. A plain numeric result makes the day count visible. If the result should show a label, such as “9 days,” keep the numeric calculation in its own cell and apply a custom number format, rather than converting the value to text; keeping it numeric makes later calculations easier.
Elapsed days versus inclusive date counts
A frequent source of confusion is whether to count both the first and last dates. Subtracting 1 January from 10 January returns 9 because nine 24-hour intervals separate midnight at the start of those dates. If a report asks how many calendar dates are included in a period and both endpoints count, add one to the elapsed-day result.
With the start date in A2 and the end date in B2, use =B2-A2+1 for an inclusive count. For 1 January through 10 January 2025, this returns 10. This convention is useful for occupancy nights only when the definition truly includes both endpoints; hotel nights, for instance, commonly count nights rather than both calendar dates. Confirm the business meaning before adding one.
For a single date in A2, use =TODAY()-A2 to calculate elapsed days from that date to today. TODAY updates when Excel recalculates the workbook, so the result changes over time. If the calculation must remain fixed for a report, enter a fixed end date in a cell instead of relying on TODAY.
Subtracting dates that include times
When cells contain both date and time, subtraction returns a fraction as well as any whole days. For example, the interval from 9:00 a.m. to 3:00 p.m. on the same date is 0.25, because six hours is one quarter of a 24-hour day. To display the elapsed time, format the result as a duration, such as a custom format of [h]:mm. The square brackets allow totals greater than 24 hours to display without wrapping back to zero.
With a start date and time in A2 and an end date and time in B2, use =B2-A2. If the cells contain 9:00 a.m. and 3:00 p.m. on the same day, the underlying result is 0.25 day, or six hours. The formula works across midnight too: a shift that begins at 10:00 p.m. and ends at 6:00 a.m. the next day is eight hours, provided the end cell contains the next day’s date.
If you need whole elapsed days from positive date-time intervals, with the start date-time in A2 and end date-time in B2, use =INT(B2-A2). This drops the fractional part, so an interval of 23 hours returns 0 and an interval of 25 hours returns 1. If instead you mean the difference between the calendar dates regardless of time, use =INT(B2)-INT(A2). These definitions can produce different answers, so choose according to what the report calls a day.
Counting working days and excluding holidays
Ordinary subtraction counts every day, including Saturdays, Sundays, and holidays. For a Monday-to-Friday workday count, use Excel’s NETWORKDAYS function. It counts working days between two dates and includes both endpoints when they are working days. This is different from elapsed-day subtraction and can be the better measure for staffing, service targets, or business schedules.
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). From Monday 6 January 2025 through Friday 10 January 2025, the result is 5. If the range includes a holiday listed in D2:D10, use =NETWORKDAYS(A2,B2,D2:D10); the listed holiday dates are excluded when they fall on weekdays in the interval. Keep the holiday cells as actual dates, and make sure the range covers the relevant holiday list.
For a work schedule whose weekend is not Saturday and Sunday, NETWORKDAYS.INTL lets you specify weekend days. With the same date cells and holiday list, use =NETWORKDAYS.INTL(A2,B2,1,D2:D10). The weekend code 1 means Saturday and Sunday, the standard weekend. Other codes and a seven-character weekend pattern can represent other schedules; consult Excel’s function help for the pattern order before using a custom one.
To calculate a due date a specified number of working days after a start date, use WORKDAY. With a start date in A2, a number of working days in B2, and holidays in D2:D10, enter =WORKDAY(A2,B2,D2:D10). WORKDAY excludes weekends and listed holidays, and does not count the start date as the first working day. If your organization follows a different weekend pattern, WORKDAY.INTL offers weekend options.
Months, years, and other date differences
Dividing a day difference by 30 or 365 is only an approximation for months or years. Calendar months have different lengths, and leap years affect annual spans. For an exact count of complete calendar months or years, Excel includes DATEDIF, which is retained for compatibility with older spreadsheet programs. Its unit argument controls what it counts; for example, “m” requests complete months and “y” requests complete years. It does not return a fractional month.
With an earlier date in A2 and a later date in B2, use =DATEDIF(A2,B2,"m") to count complete months. From 15 January to 14 February, this returns 0 complete months; from 15 January to 15 February, it returns 1. The start date must be earlier than or equal to the end date, or DATEDIF returns an error. Because month-end cases can be unintuitive, test the formula against examples that match the rule your organization uses.
For an approximate year fraction where varying month lengths matter, YEARFRAC provides alternative basis conventions. Its result depends on the chosen basis, so it is not interchangeable with a simple count of completed anniversaries. For age calculations, contract terms, or billing, define how partial months and leap days should be treated rather than assuming that a fixed number of days equals a calendar month or year.
Common date subtraction problems
- The result shows a date: Change the result cell’s number format to General or Number. The calculation may already be correct.
- The formula returns #VALUE!: One or both inputs may be text rather than Excel dates. Re-enter them as dates or convert the imported text before subtracting.
- The result is negative: Check which date is earlier. Reverse the references if the intended result is a positive elapsed interval.
- The result seems off by one: Decide whether the request means elapsed days or an inclusive count of calendar dates. Add one only for the latter.
- Dates are interpreted differently: Text such as 03/04/2025 is ambiguous across regional settings. Use an unambiguous date entry or import dates with the correct locale, then check that Excel recognizes them as dates.
- A total time rolls over after 24 hours: Use a duration format such as
[h]:mminstead of a clock-time format.
For a quick check, select an input date and temporarily format it as General. A recognized date should display as a serial number. You can also test a small known interval in a blank area of the workbook before filling a formula across a large dataset. In Excel for Mac and Excel for the web, the core subtraction formulas work the same way; the ribbon and settings locations can differ from Windows.
Choosing the right calculation
Pick the formula based on the question, not just the word “days.” Use direct subtraction for elapsed calendar days, include +1 when both endpoints count as dates, and use NETWORKDAYS when weekends should not count. For business holidays, provide a maintained holiday range. For time intervals, subtract full date-time values and display the result as a duration. For calendar months or years, use a function that expresses that specific unit rather than dividing days by an assumed month length.
Before copying a formula down, verify the date order, cell formats, and endpoint rule with one row whose answer you can calculate manually. When a holiday list is used, check that it contains actual date values and that the chosen weekend convention matches the work schedule. These checks prevent many apparently mysterious differences between two otherwise reasonable date calculations.
Frequently Asked Questions
What is the simplest formula to subtract dates in Excel?
Subtract the earlier date cell from the later one. If the start date is in A2 and the end date is in B2, enter =B2-A2. The numeric answer is the elapsed number of days.
Why does Excel show a date instead of the number of days?
The result cell is probably formatted as a date. Select it and choose Home > Number Format > General or Number. Changing the format affects how the result appears, not the underlying subtraction.
How do I count both the start and end date?
For an inclusive calendar-date count, add one to the subtraction result. Confirm that the task actually counts both endpoints; an elapsed interval or a count of nights may use a different rule.
How do I subtract dates but ignore weekends?
Use NETWORKDAYS with the start and end date cells. Add a holiday range as the third argument if applicable. The function counts working days inclusively, so a weekday at either endpoint is included.
Can Excel subtract dates that include times?
Yes. Subtract the full date-time cells. The result includes fractions of a day; format it as a duration such as [h]:mm to read hours and minutes, including totals longer than one day.
Can I subtract dates in Excel for the web or Mac?
Yes. Basic date subtraction and the functions described here work in Excel for the web and Excel for Mac as well as Microsoft 365 on Windows. The interface for number formats and workbook settings may be arranged differently.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Compare Two Columns in Excel with VLOOKUP FormulaTo compare two columns in Excel, check whether each value in the first list appears in the second…
- 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…
- ISERROR with VLOOKUP: How to Handle Lookup ErrorsYes. In Excel, ISERROR can test a VLOOKUP result and report whether it returns an error; wrap the…
- Excel MIN With Criteria: Formulas, Examples, and FixesTo find the smallest value in Excel that meets a condition, use MINIFS: =MINIFS(B2:B10,A2:A10,"East"). It returns the minimum…
- Excel FV Formula: How to Calculate Future Value and GrowthIn Excel for Microsoft 365, use the FV function to calculate the future value of regular payments or…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.