To find the number of days between two dates in Excel, subtract the earlier date from the later one. With the start date in A2 and the end date in B2, enter =B2-A2 in another cell. This returns the elapsed days, not a count that includes both endpoint dates. Before you begin, make sure Excel recognizes your entries as dates rather than text.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The same basic date-subtraction formula works in Excel for Mac and Excel for the web, though ribbon layouts can differ slightly.
- A start date and an end date in separate cells. For example, put the start date in A2 and the end date in B2.
- A blank cell for the result. If you want to calculate several date pairs, you can fill the formula down a column.
Step-by-Step
- Enter the two dates in separate cells. Put the earlier or starting date in A2 and the later or ending date in B2. Enter dates in a form Excel recognizes, such as 3/1/2026 or 1-Mar-2026. The way a date is displayed depends on your regional settings, so a month-name format can help avoid ambiguity. If the dates are already in your worksheet, identify which cells contain the start and end dates before writing the formula.
- Choose a result cell. Click a blank cell where you want the day count to appear, such as C2. Keeping the result beside the date pair makes it easier to review and copy the calculation later. Do not put the formula in either date cell, because that would replace one of the values you need to calculate with.
- Subtract the start date from the end date. With the start date in A2 and the end date in B2, enter
=B2-A2in C2, then press Enter. Excel stores real dates as serial numbers, so subtracting the earlier date from the later date gives the number of elapsed calendar days between them. For example, if A2 contains 1-Mar-2026 and B2 contains 10-Mar-2026, the result is 9: nine 24-hour date steps separate the two dates. - Check the result’s number format. Excel normally displays a subtraction result as a number, but a cell previously formatted as a date may show an unexpected date instead. Select C2, then choose Home > Number Format and select General or Number. The value should display as a day count. Number format changes how a value appears; it does not change the calculation itself.
- Decide whether you need elapsed days or an inclusive count. Ordinary subtraction excludes the starting day from the count. If a project runs from March 1 through March 10 and you want to count both March 1 and March 10 as calendar dates, use
=B2-A2+1. With the start date in A2 and the end date in B2, this returns 10 for those example dates. Use the inclusive version for things such as counting calendar dates in a booking period when both arrival and departure dates are meant to count. Confirm the counting convention before using the result in a report. - Copy the calculation for more rows, if needed. If each row has a start date in column A and an end date in column B, select C2 and use the fill handle, the small square at the lower-right corner of the selected cell, to drag the formula down. With dates in A2:A20 and B2:B20, the formula entered in C2 adjusts its references for each row, so C3 calculates from A3 and B3. You can also copy C2 and paste it into the cells below. Review a few results to make sure each row aligns with the intended date pair.
- Handle dates in the opposite order deliberately. If the end date is earlier than the start date, subtraction returns a negative number. That can be useful because it flags a reversed date pair. If you only need the distance between the dates regardless of which comes first, use
=ABS(B2-A2). With dates in A2 and B2, ABS returns the positive difference; for example, dates 9 days apart produce 9 even if the earlier date is in B2. Do not use this version when the sign is meaningful, such as when identifying overdue or incorrectly ordered dates.
Excel for Mac and Excel for the web use the same subtraction and ABS formulas. On Mac, use the Home tab’s number-format controls if the result is displaying as a date. In Excel for the web, select the result cell and use the Number Format controls on the Home tab. Menu wording or the placement of controls can vary slightly by version, but no special date-difference function is required for this calculation.
Common Mistakes
- Reversing the subtraction. The end date goes first and the start date second: end minus start. Reversing them produces a negative result when the start date is earlier. A negative value is not automatically an error, but check the cell order if you expected a positive count.
- Expecting subtraction to count both dates. The result from March 1 to March 10 is 9 elapsed days, not 10 inclusive calendar dates. Add 1 only when your definition specifically includes both the first and last dates.
- Using dates stored as text. A value that looks like a date may not be recognized as one. Text-imported data, leading apostrophes, or an unrecognized regional format can prevent normal date arithmetic. Check whether Excel aligns the value as expected and whether changing its number format changes its appearance.
- Misreading a result formatted as a date. A result such as 9 may appear as a date if the result cell has a date format. Switch the result to General or Number before deciding that the calculation is wrong.
- Confusing calendar days with workdays. Simple subtraction includes weekends and holidays. It does not calculate business days, and it does not account for partial days or time-of-day differences in the way a work-hours calculation would.
Tips
- Label the columns. Use headings such as Start Date, End Date, and Days Between. Clear labels reduce the chance of subtracting the wrong cells, especially when a worksheet has many date columns.
- Use an unambiguous date display. A format such as 1-Mar-2026 makes the month clear to a reader. To change the display, select the date cells and open the Number Format list on the Home tab. Formatting changes the display, not the stored date value.
- Choose and document a counting rule. Decide whether your task needs elapsed days or inclusive calendar days. For example, elapsed days are often used to measure how much time has passed, while a schedule or date range may count both its first and last dates. A short note in the worksheet can prevent different readers from interpreting the total differently.
- Check boundary cases. Try a pair of consecutive dates and a pair with the same date. Consecutive dates should return 1 elapsed day; identical dates should return 0. If you are using an inclusive formula, those results become 2 and 1 respectively.
- Remember that dates can include times. Excel date-time values contain a whole-number date portion and a fractional time portion. If the cells include times, subtraction can return a decimal number of days. Format the result as Number to see the fraction, or decide whether your task should use date-only values or a rounded whole-day count.
Troubleshooting
The formula displays as a date. Select the formula cell and change its number format to General or Number from Home > Number Format. The underlying answer may already be correct; Excel is simply displaying the numeric result using a date style.
The formula returns an error or does not calculate. First check that both referenced cells contain valid Excel dates and that the formula starts with an equals sign. Confirm that the cell references match your worksheet; if your dates are in D4 and E4, a formula referring to A2 and B2 will calculate from the wrong cells. If the formula appears literally in the cell instead of returning a result, change the cell from Text to General, then re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles formula display, while the equivalent key combination on Mac can vary with keyboard layout.
The result is negative. The date in the first reference is later than the date in the second. Verify which cell is the start date and which is the end date. If you intentionally need only the absolute distance, use the ABS version in the steps above; otherwise, keep the negative value as a warning that the dates are reversed.
The date looks right but subtraction fails. Select the date cell and inspect the formula bar. If the value was imported or pasted as text, try converting it to a real date using Excel’s date-conversion options or re-enter it in a recognized format. Avoid applying a date format as the only fix: formatting text to look like a date does not always convert its underlying value. If the dates use a different regional order, confirm whether an entry such as 04/05/2026 means April 5 or May 4 before converting it.
The result is a decimal. The original values may include times, such as a start time in the morning and an end time later in the day. A difference of 1.5 means one and a half days. If you require full calendar-date counts, remove the time portions or use a date-only calculation appropriate to your reporting rule. Do not round automatically until you know whether partial days should count as a full day, be ignored, or remain fractional.
Frequently Asked Questions
Does Excel have a function specifically for days between two dates?
For a straightforward calendar-day difference, subtraction is the simplest approach. With the start date in A2 and end date in B2, =B2-A2 returns elapsed days. A separate function is not necessary for this basic calculation.
How do I count both the start and end dates?
With the start date in A2 and the end date in B2, use =B2-A2+1. This includes both endpoints. For a date range where both dates are March 1 and March 10, the inclusive count is 10 calendar dates.
How can I find the number of weekdays between dates?
Use Excel’s NETWORKDAYS function when you need working days rather than all calendar days. It excludes Saturdays and Sundays by default, and can also exclude a supplied list of holidays. Confirm that the function’s weekend convention matches your work schedule; a standard Monday-to-Friday workweek is assumed by default.
Why does subtracting the dates show a negative number?
The date used as the end date is earlier than the date used as the start date. Check the cell references and the order of the dates. If the order is intentionally unknown and only the distance matters, wrap the subtraction in ABS to return a positive number.
Will the formula work in Excel for Mac or Excel for the web?
Yes. Basic date subtraction and the ABS function work in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula syntax is the same; only some interface controls may be in different places.
Can the answer include part of a day?
Yes. If either date cell also contains a time, Excel stores that time as a fraction of a day, so subtraction can return a decimal. For example, half a day is 0.5. Decide whether your calculation should preserve partial days or report whole calendar days before changing or rounding the result.
Related
- Export Tab Names in Excel: Two Practical Ways to Save Them
- How to Export an Excel Table to Word Without Losing Format
- MT5 History to Excel: Export Trades and Account Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add Page Numbers in Microsoft Word 365To add page numbers in Word 365 on Windows, open the document, select Insert > Page Number, choose…
- Split Text in Google Sheets: Methods, Steps, and TipsTo split text in Google Sheets, select the cells you want to separate, choose Data > Split text…
- Excel Table Dropdown List: Create and Manage ListsTo add a dropdown list to an Excel table, select the cells in its column, choose Data >…
- How to Add a Cover Page in Word 365: Steps and TipsTo add a cover page in Word 365, open your document, go to Insert > Cover Page, and…
- Excel Keyboard Shortcuts: How to Work Without a MouseYes. In Excel for Microsoft 365 on Windows, you can navigate worksheets, select cells, enter and format data,…
- Play a PowerPoint Slideshow on Loop: Setup and TipsTo play a PowerPoint slideshow continuously, open the presentation in the desktop app, choose Slide Show > Set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.