To get the number of days between two dates in Excel, enter the later date in one cell and the earlier date in another, then subtract the earlier date from the later one. For example, if the start date is in A2 and the end date is in B2, enter =B2-A2 in another cell. Excel returns 9 for January 1 to January 10, 2025. The result counts elapsed days, not both calendar dates; you can add 1 if you need an inclusive count.
What You’ll Need
- A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The same subtraction method works in Excel for Mac and Excel for the web.
- Two cells containing dates Excel recognizes as dates, such as a start date and an end date.
- An empty cell for the result. You can also use a third column if you are calculating the difference for many rows.
No special add-in or date-difference function is required. Excel stores recognized dates as serial numbers, so subtracting one date from another returns the number of days between them.
Step-by-Step
- Enter the dates in separate cells. Put the earlier, or start, date in A2 and the later, or end, date in B2. For example, enter 1/1/2025 in A2 and 1/10/2025 in B2. The way a date appears depends on your regional settings, so confirm that Excel has interpreted each entry as a date and not as plain text. A reliable option is to enter dates in a format Excel recognizes in your region, or use the date picker if it is available in your workflow.
- Select the cell for the answer. Click an empty cell, such as C2. Keeping the result in a separate cell preserves the original dates and makes the calculation easier to copy or audit. If your worksheet has column headings, you might label C1 as Days Between so anyone reading the sheet can understand the value.
- Subtract the start date from the end date. With the start date in A2 and the end date in B2, enter
=B2-A2in C2 and press Enter. Excel calculates January 10 minus January 1 as 9 days. The order matters: subtract the earlier date from the later date to get a positive number. Excel recalculates the result if either date changes. - Check the result and its number format. A date difference should usually display as a number, such as 9, rather than as another date. If C2 displays a date or an unexpected number format, select C2, then choose Home > Number Format and select General or Number. Number is useful if you want consistent numeric display; set decimal places to 0 for whole calendar days. The format changes how the result looks, not the calculation itself.
- Calculate differences for more rows. Put each row’s start date in column A and 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 fill it down. Excel adjusts the references for each row, so the next row compares A3 with B3, then A4 with B4, and so on. Double-clicking the fill handle can fill the formula down alongside a neighboring column of data.
- Choose whether to count both dates. Simple subtraction measures elapsed days, so a start and end date that are the same returns 0. If your task counts every calendar date touched, including both the first and last dates, add 1 to the difference. With the start date in A2 and end date in B2, use
=B2-A2+1. For January 1 through January 10, this returns 10. Use this only when an inclusive count is what you intend; elapsed time and inclusive date counts answer different questions. - Decide how to handle reversed dates. If the end date is earlier than the start date, the ordinary subtraction formula returns a negative result. That can be helpful for spotting data entry errors. If you only want the size of the gap regardless of which date comes first, use the absolute difference. With the two dates in A2 and B2, enter
=ABS(B2-A2)in C2. This returns a positive number, but it does not tell you which date is earlier, so use it only when direction does not matter.
For a basic elapsed-day calculation, the direct subtraction formula is usually the clearest choice. It counts calendar days, including weekends and holidays. It does not count only business days; that is a different calculation and requires a workday-aware approach.
Common Mistakes
- Reversing the subtraction. Typing the start-date cell first and the end-date cell second produces a negative result when the end date is later. Check that the later date is on the left side of the minus sign and the earlier date is on the right.
- Expecting an inclusive count. From January 1 to January 10, elapsed days equal 9, while counting January 1 and January 10 as included dates gives 10. Decide which definition your task uses before adding or removing a day.
- Using dates stored as text. A value that looks like a date may not be a usable Excel date. Text can cause a #VALUE! error or prevent the formula from calculating as expected. Re-enter the date in a recognized format or convert the text to a real date before subtracting.
- Leaving a date format on the answer cell. Excel can display the numeric result using a date-style format, making a correct calculation look wrong. Set the result cell to General or Number before troubleshooting the arithmetic.
- Forgetting that ordinary subtraction includes weekends. A difference of 7 means seven calendar days, not seven workdays. Do not use this result as a business-day count if Saturdays, Sundays, or holidays should be excluded.
Tips
- Use clear column headings. Labels such as Start Date, End Date, and Elapsed Days make it easier to confirm which cells the formula should reference, particularly in a shared workbook.
- Keep date cells consistent. Apply one date display format to the start and end columns using Home > Number Format. Consistency makes unusual entries easier to spot, although changing a cell’s display format does not convert text into a real date by itself.
- Use a Table for a growing list. Select the data and choose Insert > Table. When you add a formula in the calculated column, Excel can extend it to new rows. Check the first few results after pasting new data, especially if the pasted dates may be text.
- Distinguish calendar days from workdays. Basic date subtraction includes every day on the calendar. If you need to exclude weekends and possibly specified holidays, use Excel’s workday-counting options rather than interpreting the basic subtraction result as working days.
- Account for times if cells contain them. Excel dates can include a time fraction. If your cells contain times as well as dates and you want whole calendar days only, remove the time portion from both values before subtracting. With the start date and time in A2 and the end date and time in B2, enter
=INT(B2)-INT(A2). For January 1 at 11 p.m. through January 2 at 1 a.m., this returns 1 calendar day; subtracting the full date-time values instead gives 0.0833 days, or about two hours.
Troubleshooting
The formula returns #VALUE!. One or both cells may contain text rather than dates Excel can calculate with. Click each date cell and inspect the formula bar; look for a leading apostrophe or other signs that the value was imported as text. Try re-entering the date in the cell using a recognized format. If the dates came from a text file, use Data > Text to Columns to convert them where appropriate, and verify the result before applying the subtraction formula again.
The answer looks like a date. Select the result cell and choose Home > Number Format > General or Number. If the displayed result is still surprising, check the formula bar to confirm the formula references the intended start and end cells. Formatting cannot correct reversed cell references or invalid source values.
The answer is negative. The date on the right side of the subtraction is earlier than the date on the left. Confirm the dates and decide whether a negative result is a useful warning. If you intentionally want the absolute gap without regard to order, use the ABS option described above.
The answer is a decimal. One or both values probably include times. Excel stores the time as part of the date value, so subtraction can return a fraction of a day. If the task requires elapsed hours, the fraction may be useful; if it requires whole calendar dates, use the INT-based formula in Tips. You can also set the display to zero decimal places, but number formatting only hides decimals—it does not remove the time from the calculation.
The formula does not update after changing a date. Check that calculation is set to automatic: choose Formulas > Calculation Options > Automatic. Also make sure the formula cell contains a formula beginning with an equals sign rather than a text entry. In Excel for Mac, the same formula works; ribbon labels and placement may vary slightly by release. Excel for the web also supports the formula, though its ribbon layout differs from the Windows desktop app.
Frequently Asked Questions
Does Excel have a function for subtracting dates?
For the number of days between two dates, you usually do not need a separate function. Subtract the earlier date from the later date with a standard formula. Excel’s date serial values make this direct subtraction work.
Why does January 1 to January 10 return 9, not 10?
The result measures elapsed days between the dates. Ten days have not elapsed at the beginning of January 10; the interval from January 1 to January 10 is 9 days. Add 1 only when you specifically need to count both the start date and end date as included calendar dates.
How do I calculate days from a date to today?
Put the original date in A2 and enter a formula in B2 that subtracts A2 from today’s date. The result updates when the workbook recalculates because TODAY returns the current date. If you need a fixed snapshot, copy the result and use Paste Special > Values so it no longer changes each day.
How do I get a positive number when dates might be in either order?
Use the absolute difference formula shown in Step 7. It reports the size of the gap as a positive value whether A2 or B2 is earlier. Keep in mind that this hides the order of the dates, which may matter when you are checking schedules or deadlines.
Can I subtract dates to get business days?
Not with ordinary subtraction alone. That counts Saturdays, Sundays, and holidays along with weekdays. Use a workday-counting method when you need weekdays only, and provide a holiday list if those dates should also be excluded. Confirm whether the first and last dates should count under your organization’s rules.
Will the formula work in Excel for Mac or Excel for the web?
Yes. Basic date subtraction is supported in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula is the same; only ribbon locations and interface details may differ.
Related
- How to Search in Google Sheets
- Search in Excel: Keyboard Shortcuts, Find and Replace
- Save an Excel Workbook as a Copy Without Replacing It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Select a Column on Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Spacebar to select the entire column containing the active cell.…
- Excel Not Equal: The <> Operator ExplainedIn Excel, “not equal to” is written as <>. Put it between two values or cell references to…
- Excel Hide Row and Column Labels: Steps and OptionsTo hide the row numbers and column letters in Excel for Microsoft 365 on Windows, open the View…
- How to Rename a Sheet in Excel Using the KeyboardIn Excel for Microsoft 365 on Windows, select the worksheet you want to rename, then press Alt, H,…
- Center in Excel Without Merging: A Practical GuideTo center a heading across several Excel columns without merging cells, select the range, open Format Cells with…
- How to Delete an Excel File from a SharePoint SiteTo delete an Excel workbook from SharePoint, open the document library that contains it, select the workbook, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.