Day Count in Excel: Calculate Days Between Dates

To get the number of elapsed calendar days between two dates in Excel, subtract the earlier date from the later one. With a start date in A2 and an end date in B2, enter =B2-A2 in C2. For a count that includes both the start and end dates, add 1. The steps below also show how to count weekdays, exclude holidays, and fix common date problems.

What You’ll Need

An Excel workbook in Microsoft Excel for Microsoft 365 on Windows. The basic date-subtraction method also works in Excel for Mac and Excel for the web.
A start date and an end date entered in cells, ideally in separate columns. For example, label A1 Start date and B1 End date, then enter the dates in A2 and B2.
If you want working days rather than calendar days, a clear decision about which days count as weekends and a list of any holidays to exclude.

Step-by-Step

Set up the date columns. Put each start date in column A and its matching end date in column B. Use one row per period, with the first pair in A2 and B2. Add headings such as Start date, End date, and Day count so the result is easy to interpret. If you already have dates in another layout, you can use the same method by changing the cell references in the formula.
Enter real Excel dates. Type dates in a form Excel recognizes for your regional settings, such as 3/1/2025 or 1-Mar-2025. A date that looks correct on screen may instead be text, especially if it was pasted from a website or imported from another system. If Excel aligns the entry to the left by default or changing its date format has no effect, check the troubleshooting section before relying on the result.
Choose what day count you mean. Subtracting dates gives elapsed days: the start date itself is not counted as a full elapsed day. For example, the time from March 1 to March 2 is one day. Some tasks, such as counting calendar dates covered by a booking, count both endpoints and therefore need an inclusive count. Decide which meaning fits your task before filling down a formula.
Calculate elapsed calendar days. With the start date in A2 and the end date in B2, enter =B2-A2 in C2. Press Enter. For a start date of March 1, 2025, and an end date of March 10, 2025, the result is 9 elapsed days. Excel stores dates as serial numbers, so subtraction finds the difference between those numbers. This simple formula counts every calendar day, including Saturdays, Sundays, and holidays.
Count both the start and end date when appropriate. With the start date in A2 and the end date in B2, enter =B2-A2+1 in C2. The March 1 through March 10 example returns 10 dates because both March 1 and March 10 are included. Use this version for a date range where each listed calendar date counts, such as a stay spanning those dates. Do not add 1 automatically: for measuring elapsed time between two moments or dates, the unadjusted subtraction is usually the intended result.
Count weekdays instead of all calendar days. With the start date in A2 and end date in B2, enter =NETWORKDAYS(A2,B2) in C2. NETWORKDAYS counts Monday through Friday and includes the start and end dates when they fall on weekdays. For January 1 through January 10, 2025, the result is 8 weekdays: Wednesday, Thursday, and Friday in the first week, plus Monday through Friday in the next week. Saturdays and Sundays are excluded, but public holidays are not excluded unless you supply them.
Exclude specified holidays. With the start date in A2, end date in B2, and holiday dates in D2:D3, enter =NETWORKDAYS(A2,B2,D2:D3) in C2. If D2 contains January 1, 2025, and D3 contains January 6, 2025, the January 1 to January 10 example returns 6 working days. The holiday cells must contain actual Excel dates. Make sure the list reflects the holidays relevant to your location or organization; Excel does not choose a holiday calendar for you.
Copy the calculation down and check the result format. Select the cell with the formula and drag the small fill handle at its lower-right corner down the rows, or copy and paste it. Excel adjusts relative references for each row. If the result appears as a date instead of a number, select the result cells and choose Home > Number Format dropdown > General or Number. You can also open Format Cells with Ctrl+1 on Windows; on Mac, use Command+1. A day-count result should normally display as a whole number.
Check the date order and test a known pair. For a forward date range, the end date should be later than or equal to the start date. Confirm one result by counting the dates independently, especially if the inclusive rule or holiday list matters. If you need to show a negative number when the end date precedes the start date, ordinary subtraction can do so, but a cell formatted as a date may display an unexpected result. Consider correcting the input order or using a clear error-checking approach for your workbook.

Common Mistakes

Mixing elapsed days with inclusive dates: This is the most frequent source of an answer that seems off by one. March 1 to March 10 is 9 elapsed days, but there are 10 calendar dates if both endpoints count. Decide which question you are answering and use either subtraction or subtraction plus 1 consistently.

Using NETWORKDAYS when calendar days are needed: NETWORKDAYS deliberately ignores Saturdays and Sundays. If a delivery period, age calculation, or calendar span includes weekends, use date subtraction instead. Conversely, subtracting dates does not give a business-day total.

Assuming holidays are removed automatically: NETWORKDAYS excludes standard Saturday-Sunday weekends, not national, regional, or company holidays. Add those dates as a range in the formula. Check that the holiday range does not accidentally include headings or unrelated dates.

Entering dates as text: A text value may look like a date but fail in subtraction or be interpreted differently from what you intended. Regional settings affect whether entries such as 4/5/2025 mean April 5 or May 4. Using an unambiguous entry such as 5-Apr-2025 can help, but confirm Excel has stored it as a date value.

Leaving the answer formatted as a date: Excel may inherit a date format from a copied cell. The underlying difference can be right while the displayed result looks like a date in 1900 or another unexpected year. Change the result cell to General or Number rather than changing the formula.

Including times without realizing it: Excel stores a time as a fraction of a day. If either cell contains a time, subtraction can return a decimal such as 2.5. That may be correct for elapsed time, but it is not a whole-day count. Decide whether partial days matter before rounding or removing time values.

Tips

Use clear column headings that state the counting rule, such as Elapsed days, Inclusive calendar days, or Working days. That prevents someone reading the workbook from assuming that every total has the same definition. If the workbook supports a process with a formal rule, document that rule next to the calculation.

Keep the date cells formatted as dates and the answer cells formatted as numbers. To display dates in a consistent style, select the date cells and use Home > Number Format dropdown > Short Date. Formatting changes how a value appears; it does not convert text into a true date or change the arithmetic itself.

For a repeated list, use a single formula pattern and fill it down rather than typing each answer by hand. Check that the formula in the next row refers to that row’s dates. If you need the same holiday list for many rows, use an absolute reference so it stays fixed when copied. With start dates in A2:A20, end dates in B2:B20, holidays in D2:D10, and the formula entered in C2, use =NETWORKDAYS(A2,B2,$D$2:$D$10). The dollar signs lock the holiday range while the start and end references change as the formula is copied down.

If your weekend is not Saturday and Sunday, NETWORKDAYS.INTL lets you specify a different weekend pattern. Because weekend codes and custom weekend strings can be easy to misconfigure, check the function’s Excel help for the exact pattern that matches your schedule. Do not simply remove days by hand if the weekend pattern is likely to change.

When importing dates, verify a few source values before applying a formula to thousands of rows. Look for blanks, dates stored as text, mixed date conventions, and end dates that precede start dates. A quick check on representative rows is more useful than assuming that consistent-looking formatting means consistent underlying data.

Troubleshooting

The formula returns #VALUE!: One or both inputs may be text rather than numeric date values. Try entering the dates directly into fresh cells using a format Excel recognizes, then test the formula again. If the values came from an import, use Excel’s data-conversion tools or re-import with the correct date locale rather than relying on a display format alone.

The answer is a date or an odd-looking number: Select the answer cell and change its number format to General or Number. A formula such as subtraction returns a numeric serial difference, but Excel can display that number using the cell’s existing date format. The formula usually does not need to change.

The result is negative: Check that the start and end dates are in the intended columns and that neither was entered with the wrong year. A negative result simply means the first date is later than the second. If reverse order should never occur, correct the inputs or add a separate validation check instead of hiding the negative value.

The answer is one day too high or low: Compare the result with the rule you intended. Plain subtraction counts the gap between dates; adding 1 counts both endpoints. For working days, remember that NETWORKDAYS includes weekdays at both ends and may exclude listed holidays. Check whether either endpoint falls on a weekend or appears in the holiday list.

The result includes a decimal: Inspect the cells for hidden times. For a whole-date difference that should ignore time portions, with date and time values in A2 and B2, enter =INT(B2)-INT(A2) in C2. For example, if the values are March 1 at 6:00 PM and March 3 at 9:00 AM, this returns 2 calendar-date steps, while direct subtraction returns 1.625 days. Use this only when the time of day should be disregarded; if elapsed hours matter, retain the decimal or format it appropriately.

NETWORKDAYS gives an unexpected total: Confirm the holiday range contains real dates and only the holidays you mean to exclude. Check that the dates are within the period and that the weekend pattern is the standard Saturday-Sunday pattern assumed by NETWORKDAYS. The function can return zero if the range contains no weekdays after weekends and holidays are excluded.

Frequently Asked Questions

How do I count 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 in another cell. This returns elapsed calendar days and excludes the start date as a counted day.

How do I count both dates in the range?
With the start date in A2 and the end date in B2, use =B2-A2+1. This includes both endpoints, so a one-day event that starts and ends on the same date returns 1.

How can I count only working days?
With the start date in A2 and end date in B2, use =NETWORKDAYS(A2,B2). It counts Monday through Friday, including weekday endpoints. Supply a holiday range as the third argument if holidays should be skipped.

Can Excel count days when the dates are on different sheets?
Yes. In the formula, refer to the date cells on their respective sheets, for example =EndSheet!B2-StartSheet!A2. Replace EndSheet and StartSheet with the actual worksheet names. If a sheet name contains spaces, enclose it in single quotes, as in ='Project Dates'!B2.

Why does Excel show a date instead of a day count?
The result cell is probably formatted as a date. Change its number format to General or Number. Keep the source cells formatted as dates so the inputs remain readable.

Does the day-count formula work in Excel for Mac or the web?
Yes. Date subtraction and NETWORKDAYS work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon labels and keyboard shortcuts can vary slightly by platform, but the formulas are the same.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.