To calculate elapsed days between two dates in Excel, put the earlier date in A2 and the later date in B2, then enter =B2-A2 in another cell. For complete months or years, use DATEDIF; for weekdays, use NETWORKDAYS. First make sure Excel recognizes both entries as dates, not text.
What You’ll Need
- A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The basic date formulas also work in current Excel for Mac and Excel for the web, though ribbon layouts can differ.
- A cell for the start date and a cell for the end date. The examples below use A2 for the start date and B2 for the end date.
- Optional: a list of holiday dates if you want a workday count that excludes holidays as well as weekends.
Step-by-Step
- Enter and check the two dates. Put the start date in A2 and the end date in B2. For example, enter January 6, 2025 in A2 and January 10, 2025 in B2. Excel may display the dates according to your regional settings, such as 1/6/2025 or 06-Jan-2025. The important thing is that Excel stores them as date values. Select each cell and check that its value behaves like a date: changing the number format to General should show a number, not leave the typed date unchanged as text.
- Subtract the start date from the end date for elapsed days. With the start date in A2 and the end date in B2, enter
=B2-A2in C2. For January 6 to January 10, 2025, the result is 4 days. This counts the time between the dates, not both calendar dates inclusively: the intervals are January 6 to 7, 7 to 8, 8 to 9, and 9 to 10. If the result appears as another date, select C2 and choose Home > Number Format drop-down > General or Number. - Use DAYS if you prefer a function. With the start date in A2 and the end date in B2, enter
=DAYS(B2,A2). It returns the same elapsed-day result as subtracting the dates: 4 for the example above. DAYS takes the end date first and the start date second, which can feel backward if you expect to enter dates in chronological order. The subtraction formula is often easier to scan, while DAYS can make the purpose clear to someone reading the worksheet. - Choose whether your count should include both dates. Ordinary subtraction counts the gap between dates. If you need to count every calendar date touched, including the start and end dates, add 1 to the difference. With the start date in A2 and the end date in B2, enter
=B2-A2+1. January 6 through January 10, inclusive, returns 5. This is useful for counting calendar days in a date range, but do not add 1 automatically: a question about elapsed time usually expects 4 days for those dates. - Calculate complete months or years. DATEDIF returns the number of complete units between two dates. With the start date in A2 and the end date in B2, enter
=DATEDIF(A2,B2,"m")for complete months or=DATEDIF(A2,B2,"y")for complete years. For January 15, 2020 in A2 and March 10, 2023 in B2, the month formula returns 37 and the year formula returns 3. These are completed periods, not the number of calendar-month boundaries crossed or a decimal fraction of a year. DATEDIF remains available in Excel, even though it may not appear in the function autocomplete list. - Show a years-and-months breakdown when that is more useful. With the start date in A2 and the end date in B2, enter
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months". For January 15, 2020 through March 10, 2023, the result is 3 years, 1 month: three full years pass by January 15, 2023, and one more full month passes by February 15. The remaining days are not included in this display. Because the formula joins numbers with words, Excel returns text; use a separate numeric formula if you need to sort or calculate with the result. - Count weekdays, excluding weekends. Use NETWORKDAYS when you want the number of working days in a date range. With the start date in A2 and the end date in B2, enter
=NETWORKDAYS(A2,B2). For January 6 through January 10, 2025, it returns 5 because the range contains five weekdays. Unlike simple subtraction, NETWORKDAYS includes both endpoints when they are working days. If either endpoint falls on a weekend, it is not counted. - Exclude holidays from the weekday total. Put actual holiday dates in C2:C10, leaving unused cells blank. With the start date in A2, end date in B2, and holiday dates in C2:C10, enter
=NETWORKDAYS(A2,B2,C2:C10). The result is the weekday count minus any listed holidays that fall on weekdays within the range. Check that the holiday cells contain real Excel dates, not text labels. NETWORKDAYS assumes Saturday and Sunday are the weekend; if your weekend days differ, use NETWORKDAYS.INTL and consult its weekend-code options. - Copy the calculation down for more date pairs. If each row has a start date in column A and an end date in column B, place the selected formula in C2 and drag its fill handle down. Excel adjusts the references for each row, so the next row uses A3 and B3. To keep a holiday list fixed while copying a NETWORKDAYS formula, select the holiday range reference in the formula and press F4 on Windows to make it absolute. On many Mac keyboards, use Command+T to cycle reference styles; if that shortcut is intercepted by macOS or your keyboard, add dollar signs manually. The fixed reference should look like
$C$2:$C$10.
Common Mistakes
- Reversing the dates. The start date must come first in DATEDIF and NETWORKDAYS. In simple subtraction, the end date goes first: end minus start. Reversing the order can produce a negative result or an error.
- Confusing elapsed days with an inclusive count. Subtracting January 6 from January 10 gives 4, while counting January 6, 7, 8, 9, and 10 gives 5. Choose the rule that matches the question before adding 1.
- Reading a date-formatted answer as a wrong result. Excel stores dates as serial numbers. If the formula cell is formatted as Date, a result such as 4 may display as a date in 1900. Change the result cell to General or Number to see the day count.
- Expecting DATEDIF months to be calendar months touched. DATEDIF with the m unit counts completed months. From January 31 to February 1, it does not mean a full month has passed. It is also not a decimal-month calculation.
- Using text that looks like a date. A value pasted from a website or imported file may look like a date but be stored as text. Excel may then return #VALUE! or fail to calculate as expected. Convert the source values before relying on the result.
- Ignoring time values. A cell can contain a date and a time even if its format shows only the date. Subtraction then includes fractions of a day. Format the result as a number to see the fraction, or remove times from the source if the task requires whole calendar days.
Tips
- Label the input columns Start date and End date, and label the result according to its meaning, such as Elapsed days or Workdays. Clear labels help prevent someone from treating a weekday count as a calendar-day count.
- Use a consistent date-entry style, such as 2025-01-06, especially when sharing a workbook across regions. Excel’s interpretation of entries like 3/4/2025 can vary with date and language settings.
- Format input cells as Date before entering or pasting dates. Formatting alone does not convert text that is already stored as text, but setting the format first can make new entries clearer.
- For a changing list of rows, convert the data range to an Excel table with Insert > Table. A formula entered in a table column generally fills down for that column as new rows are added.
- Keep a separate result for each question. For example, use one column for elapsed calendar days and another for weekdays. A single cell that mixes date counts and explanatory text is harder to reuse in later calculations.
- For a reusable formula, select the result cell and check the formula bar before copying it. Confirm that each row points to the intended start and end cells, particularly after inserting columns or moving data.
Troubleshooting
The formula displays a date instead of a number: Select the result cell, then choose Home > Number Format drop-down > General or Number. The calculation may be correct; its display format is not appropriate for a duration.
The result is #VALUE! or does not change: Check whether A2 or B2 contains text rather than a date value. Try selecting a source cell and changing its format to General. If it remains visibly unchanged as text, convert it. One option is to select the affected range and use Data > Text to Columns, then choose the appropriate date order on the conversion screen and finish the conversion. Check the result before applying that method to a large imported range.
The formula returns a negative number: Confirm that the end date is later than the start date and that the references are in the intended order. A negative answer can be meaningful for a reversed interval, but if you want a nonnegative span, correct the source dates rather than hiding the issue.
DATEDIF returns #NUM!: DATEDIF requires the start date to be earlier than or equal to the end date. Correct the date order. Also check that neither date is invalid or stored as text.
NETWORKDAYS seems one day too high or low: It counts working days inclusively, so compare it with your intended rule. Check whether the first or last date is a weekday, and make sure the holiday range includes the relevant dates. A listed holiday on a Saturday or Sunday does not reduce the weekday total.
The result contains a decimal: One or both source cells probably include a time. If hours matter, retain the decimal and format it appropriately. If only whole days matter, decide whether the task needs complete 24-hour periods or calendar-date boundaries; those are different calculations, so do not round without confirming the intended meaning.
The formula appears as text instead of calculating: Make sure the result cell is not formatted as Text, remove any leading apostrophe, then re-enter the formula. Also check that calculation is enabled under Formulas > Calculation Options > Automatic.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
Put the start date in A2 and the end date in B2, then enter =B2-A2. Format the answer as General or Number. This gives elapsed days and does not count both endpoints.
How do I count both the start date and end date?
With the start date in A2 and end date in B2, enter =B2-A2+1. This inclusive count is appropriate when every calendar date in the range should count, including both endpoints.
Can Excel calculate the difference in months or years?
Yes. With the start date in A2 and end date in B2, use =DATEDIF(A2,B2,"m") for complete months or =DATEDIF(A2,B2,"y") for complete years. These return completed units, not rounded estimates.
How do I count working days between dates?
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). Add a holiday range as a third argument if needed. The default weekends are Saturday and Sunday.
Why does subtracting dates give a number?
Excel stores dates as serial numbers and calculates by subtracting those values. A number is the expected result for an elapsed-day calculation. If it is displayed as a calendar date, change the result cell’s number format to General or Number.
Do these formulas work in Excel for Mac and Excel for the web?
Yes, the formulas shown are supported in current Excel for Mac and Excel for the web. The core calculations work the same way, though ribbon locations and keyboard shortcuts can vary from Excel for Microsoft 365 on Windows.
Related
- Ungroup in Excel: How to Bring Back the Ungroup Command
- How to Average Data in Excel Across Different Sheets
- Avoid Divide by Zero Errors in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Model: How to Append Tables with Power QueryTo append tables in an Excel Data Model, use Power Query to stack their rows into one combined…
- Google Sheets Dropdown From Another Sheet: Setup GuideYes. In Google Sheets, select the cell or cells for the dropdown, open Data > Data validation, choose…
- Excel Data Validation List From Another Sheet: StepsTo create an Excel data-validation dropdown from another worksheet, put the choices on that sheet, define a workbook-level…
- Excel Data Validation List: Can You Select Multiple?Not with Excel’s built-in Data Validation list: a standard dropdown lets you choose only one item per cell…
- How to Format Tick Boxes in Excel for Clear ChecklistsTo format a tick box in Excel, select the checkbox cell and use the Home tab to apply…
- Excel Goal Seek Maximum Value: Find the Actual MaximumExcel Goal Seek cannot find the maximum value of a formula by itself: it finds an input that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.