To count elapsed days between two dates in Excel, enter the earlier date in A2 and the later date in B2, then use =B2-A2 in another cell. Excel returns the number of days from A2 up to B2, not counting the start date. If you want to count both dates, use =B2-A2+1. The steps below show how to set up the calculation, choose the right counting rule, and fix common date problems.
What You’ll Need
- An Excel workbook in Microsoft Excel for Microsoft 365. The same basic subtraction formulas work in Excel for the web and Excel for Mac.
- Two cells containing valid Excel dates. For the examples below, the start date goes in A2 and the end date goes in B2.
- A blank cell for the result. If you need working days rather than calendar days, you can also list any holiday dates in a separate range.
You do not need a special add-in or a dedicated date-counting function for the basic calculation. Excel stores valid dates as serial numbers, so subtracting one date from another gives the number of days between them. The displayed date format can vary, but the underlying values need to be recognized by Excel as dates.
Step-by-Step
- Enter the start and end dates. In a new sheet, type a label such as Start date in A1 and End date in B1. Enter your first date in A2 and your second date in B2. Use an unambiguous date entry such as 15-Mar-2026, or use the date format familiar to your computer. Excel may display a date differently after you enter it, depending on your regional settings. If you are copying dates from another file or a website, check that they are actual dates and not text that merely looks like a date.
- Choose what you mean by “between.” The usual elapsed-days calculation counts the number of midnight-to-midnight intervals from the start date to the end date. For example, from 1 March to 10 March is nine elapsed days. If you are counting every calendar date touched, including both 1 March and 10 March, the answer is ten. Decide which convention fits your task before choosing a formula. For a hotel stay, age calculation, or time elapsed, excluding the start date is often appropriate; for a date-by-date schedule or inclusive leave count, both endpoints may need to count.
- Calculate elapsed calendar days by subtraction. With the start date in A2 and the end date in B2, select C2 and enter
=B2-A2, then press Enter. For example, if A2 contains 1-Mar-2026 and B2 contains 10-Mar-2026, the result is 9. The formula works because Excel represents dates as day numbers and subtracts the earlier number from the later one. You can copy the formula down the column for other date pairs; Excel adjusts the row references automatically. - Include both endpoint dates when needed. With the same layout, where A2 is the start date and B2 is the end date, enter
=B2-A2+1in C2. The example from 1-Mar-2026 through 10-Mar-2026 returns 10. Adding one includes the starting date as well as the ending date. Do not add one automatically: it changes the meaning of the count and can create an off-by-one error if you want elapsed time. - Make the result display as a number. Select the result cell, then choose Home > Number Format and select General or Number. Date subtraction normally produces a number, but a cell that has inherited a date format may show a calendar date instead of a day count. If the result looks like an unexpected date, changing the result cell to General or Number usually reveals the correct numeric difference. You can set decimal places to zero if you want a whole-day display.
- Check the order of the dates. The simple subtraction formula returns a negative number if the end date is earlier than the start date. That is useful if the negative result signals an input error. If you want a nonnegative count regardless of which cell contains the earlier date, with the two dates still in A2 and B2, use
=ABS(B2-A2). For an inclusive count regardless of order, use=ABS(B2-A2)+1. Confirm that treating reversed dates as a positive count makes sense for your particular task. - Use working days instead of calendar days when appropriate. Calendar-day subtraction includes Saturdays, Sundays, and holidays. To count weekdays from A2 through B2, with the start date in A2 and end date in B2, enter
=NETWORKDAYS(A2,B2). For 2-Mar-2026, a Monday, through 6-Mar-2026, a Friday, this returns 5. NETWORKDAYS includes both endpoints when they are weekdays and excludes Saturday and Sunday. It is useful for basic weekday counts, but it does not automatically know which dates are holidays. - Exclude listed holidays from the working-day count. Put valid holiday dates in E2:E10, with the start date in A2 and the end date in B2. Enter
=NETWORKDAYS(A2,B2,$E$2:$E$10)in C2. Excel then excludes Saturdays, Sundays, and dates in the holiday range. The dollar signs keep the holiday range fixed if you copy the formula down a list of records. Leave unused cells in the listed range blank, and make sure the holiday entries are real dates. NETWORKDAYS assumes a standard Saturday-Sunday weekend; use NETWORKDAYS.INTL if your workweek has a different weekend pattern. - Fill the formula for other rows and verify a sample. If each row contains a different start and end date in columns A and B, enter the chosen formula in C2 and drag the fill handle at the bottom-right corner of C2 down the column. Select a row you can check manually on a calendar. Confirm both the date order and whether the intended result is elapsed, inclusive, or weekdays. For a large list, also scan for blank cells, negative results, and unusually large counts before relying on the output.
Common Mistakes
Counting the endpoints inconsistently: The difference between two dates is not automatically an inclusive count. From 1 March to 10 March, subtraction gives 9 elapsed days; adding one gives 10 dates in the inclusive span. Write down which convention your report or process requires, and use it for every row.
Using text that looks like a date: Imported values can be stored as text, even when they look like dates on screen. Subtraction may return a value error or fail to calculate as expected. This often happens when data uses a different regional order, such as day-month-year in one source and month-day-year in Excel.
Leaving the result formatted as a date: The calculation can be correct while the cell displays a date-like value. Set the result to General or Number rather than changing the date inputs unnecessarily.
Mixing calendar and working days: Subtracting dates counts weekends and holidays. NETWORKDAYS excludes weekends and can exclude dates in a holiday list. These are different measures, so do not compare or label them as if they were the same.
Ignoring time values: A cell can contain a date and a time even if its format shows only the date. Subtracting date-times may produce a fraction of a day. If the values are meant to represent whole dates, remove the time portion or use the whole-date approach described in Troubleshooting.
Tips
- Use clear column headings such as Start date, End date, Elapsed days, and Inclusive days. Clear labels make it harder to mistake one counting convention for another.
- Apply a consistent date format to the input columns. Select the date cells and use Home > Number Format to choose a suitable date display. Formatting changes how a valid date appears; it does not turn text into a true date by itself.
- Keep holiday dates in a dedicated column rather than typing them directly into each formula. That makes the list easier to review and update, and an absolute reference such as
$E$2:$E$10stays in place when formulas are filled down. - If you are preparing a report, include a short note stating whether the day count is elapsed, inclusive, or working days. This prevents readers from assuming a different rule.
- Use a small, known example as a check. A date range from Monday to Friday should return 4 for elapsed calendar days and 5 for inclusive dates; NETWORKDAYS returns 5 when neither endpoint is a holiday.
Troubleshooting
The formula shows a date instead of a number. Select the result cell and choose Home > Number Format > General or Number. The formula may already be returning the right difference; the cell’s format controls how Excel displays it.
The formula returns #VALUE!. Check whether either input is text rather than an Excel date. Try selecting each input and changing its format to Short Date. If its appearance does not change as a date would, or if the formula still fails, re-enter the date directly in Excel using a format recognized by your computer. For imported data, use Excel’s data conversion tools or correct the source format before calculating; changing the display format alone does not convert text.
The answer is negative. The date in B2 is earlier than the date in A2. Correct the entries if they were reversed. If the order genuinely does not matter, use the ABS version from the steps above, but remember that it hides which date came first.
The answer is off by one. Check whether you need elapsed days or an inclusive count. The base subtraction formula excludes the start date from the number of intervals; adding one counts both dates. Also check that no time value is affecting the inputs.
The result includes a decimal fraction. If A2 and B2 contain date-times and you need a whole-day difference between their calendar dates, with those date-times in A2 and B2, enter =INT(B2)-INT(A2). INT removes the fractional time portion from each serial value before subtraction. This is appropriate only when you want to ignore the time of day; if elapsed hours matter, keep the time values and format the result to show the precision you need.
NETWORKDAYS gives an unexpected total. Confirm that the inputs and holiday entries are valid dates, that the start and end dates are in the intended order, and that your weekend is Saturday and Sunday. NETWORKDAYS counts workdays inclusively when an endpoint is a workday. If your organization uses a different weekend pattern, use NETWORKDAYS.INTL and specify the correct pattern rather than manually adjusting the answer.
Frequently Asked Questions
Does Excel have a simple function for days between two dates?
For ordinary calendar days, subtraction is the simplest method. With the start date in A2 and end date in B2, =B2-A2 returns the elapsed-day difference. You do not need a special function.
Should I add one to the result?
Add one only when the count should include both the start and end dates. For example, a date range from 1 March through 10 March contains 10 calendar dates, but only 9 elapsed days separate the two dates.
How do I count weekdays but exclude holidays?
Use NETWORKDAYS and provide a range containing the holiday dates. For example, if the dates are in A2 and B2 and holidays are in E2:E10, =NETWORKDAYS(A2,B2,$E$2:$E$10) counts weekdays while excluding those holidays.
Can I count days from a date up to today?
Yes. With the starting date in A2, enter =TODAY()-A2 in the result cell. TODAY updates when Excel recalculates, so the count changes over time. If the start date should count as day one, add 1 to the formula; check whether a future start date should be allowed to produce a negative result.
Why does my date subtraction return a number such as 45200?
That usually means the cell is displaying a date serial value rather than a date difference in the format you expected. Select the result cell and set its number format to General or Number. Also verify that the formula subtracts the start-date cell from the end-date cell.
Do these formulas work on Mac and in Excel for the web?
Yes. Basic subtraction, ABS, INT, and NETWORKDAYS formulas work in current Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon placement or appearance may differ slightly by platform, but the formula syntax and cell references are the same.
Related
- How to Color Cells in Excel Based on Values Automatically
- How to Color Negative Values in Excel Automatically
- How to Color Code Names in Excel with Conditional Formatting
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Block an Email Sender by Name in Outlook: What WorksOutlook cannot reliably block a sender using only the display name shown in the From line. To block…
- How to Archive Emails in Outlook on the Web EasilyTo archive an email in Outlook on the web, select it in your message list and choose Archive…
- How to Create an Email Signature Tag in Microsoft OutlookTo create what is commonly called a signature tag in Outlook, make an email signature: a short block…
- Google Sheets for Scheduling: Setup, Tips and LimitsYes. Google Sheets can work well for a straightforward personal, shift, project, or room schedule: put dates, times,…
- Google Sheets IF Statements: Syntax and Examples ExplainedIn Google Sheets, an IF statement uses the IF function to test a condition and return one result…
- Excel Meal Planning Template: A Practical GuideAn Excel meal planning template can be a simple weekly grid that tracks meals, portions, preparation notes, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.