Count Days in Excel: Formulas for Dates and Workdays

Formulas & functions · Updated November 20, 2026

To count calendar 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; for a count that includes both dates, use =B2-A2+1. Excel stores valid dates as numbers, so subtracting the earlier date from the later one gives the number of elapsed days. The steps below also show how to count weekdays, exclude holidays, and fix common date problems.

What You’ll Need

  • A worksheet in Microsoft Excel for Microsoft 365 on Windows.
  • A start date and, if you are counting between two dates, an end date. Enter them in separate cells so you can update the result by changing either date.
  • Optionally, a list of holiday dates if you want to count working days while excluding holidays.

No add-ins or special settings are required. The same basic formulas work in recent desktop versions of Excel and Excel for the web. Menu names and keyboard shortcuts can vary slightly on a Mac, as noted below.

Step-by-Step

  1. Enter the dates in separate cells. In a blank worksheet, type a clear label such as Start date in A1 and End date in B1. Enter the actual dates in A2 and B2, for example 1/1/2026 and 1/7/2026. Excel may display them according to your computer’s regional date settings, but they should be stored as dates rather than text. If you are calculating several rows, keep the start dates in one column and the corresponding end dates in another.
  2. Choose whether the count should include the start date. Ordinary date subtraction counts the days that pass from the start date up to the end date. For example, January 1 to January 7 is six elapsed days, even though seven calendar dates appear on a calendar when both endpoints are counted. Use the elapsed-day method for many durations and date differences. Use an inclusive count when the question is how many calendar dates a period covers, such as a booking covering both its arrival and departure dates only if that is how your policy defines the period.
  3. Calculate elapsed calendar days. With the start date in A2 and end date in B2, enter this in C2: =B2-A2. For January 1, 2026 in A2 and January 7, 2026 in B2, the result is 6. Press Enter to calculate. If you see a date instead of a number, the calculation may still be right; change the result cell’s number format in the next step.
  4. Show the answer as a number. Select the formula cell, then choose Home > Number Format and select General or Number. A plain day count is usually easiest to read as a number with zero decimal places. You can also use Ctrl+1 on Windows to open Format Cells and select a number format. On Mac, use Command+1 to open the formatting dialog. Do not change the input date cells to General just to fix the output; apply the number format to the result cell.
  5. Count both the start and end dates when needed. With the start date in A2 and end date in B2, enter this in C2: =B2-A2+1. The January 1 through January 7 example returns 7 because the calculation adds the two endpoints to the elapsed interval. If both date cells contain the same date, this inclusive formula returns 1, while ordinary subtraction returns 0. Confirm the counting rule before using this result for a report, schedule, or billing period.
  6. Count weekdays instead of all calendar days. If you want Monday-to-Friday workdays and want to exclude Saturday and Sunday, use Excel’s NETWORKDAYS function. With the start date in A2 and end date in B2, enter this in C2: =NETWORKDAYS(A2,B2). For Monday, January 5, 2026 through Friday, January 9, 2026, the result is 5. NETWORKDAYS includes both endpoints when they are workdays, and it returns zero for weekend dates that fall within the period. It does not automatically know your organization’s holidays.
  7. Exclude listed holidays from the workday count. Put real holiday dates in D2:D10, one date per cell. With the start date in A2, end date in B2, and holiday dates in D2:D10, enter this in C2: =NETWORKDAYS(A2,B2,$D$2:$D$10). For January 5 through January 9, 2026, if D2 contains January 7, 2026, the result is 4 rather than 5. The dollar signs keep the holiday range fixed if you copy the formula down. Blank cells in the holiday range are ignored.
  8. Calculate days from a date up to today, if that is your goal. This is useful for an age of a task or the number of days since an event, but the answer changes as the current date changes. With the starting date in A2, enter this in B2: =TODAY()-A2. If A2 is October 30, 2026 and today is November 3, 2026, the result is 4 elapsed days. For a count that includes today and the starting date, add one to the subtraction. TODAY updates when Excel recalculates the workbook, so it is not suitable when you need a permanently fixed historical result.
  9. Copy the calculation down for more rows. If each row has a start date in column A and its matching end date in column B, enter the appropriate formula in C2 and drag the fill handle at the lower-right corner of C2 down the column. Excel adjusts relative references for each row. For the holiday version, the dollar signs around the holiday range keep that list from shifting as the formula is copied. Check a few rows manually to confirm dates are paired correctly.
  10. Check the result against the question being asked. Ask whether the answer should be elapsed days, inclusive calendar dates, or working days. These are different calculations, not competing ways to display the same answer. Test a short, easy interval by counting it on a calendar. For example, Friday to Monday is three elapsed days, four inclusive dates, and two weekdays when there are no holidays.

Common Mistakes

Mixing up elapsed and inclusive counts: Subtraction does not count the start date as a full day. Adding one changes the rule and may be wrong for durations measured as time elapsed. Decide which definition applies before selecting a formula.

Entering dates as text: A value that looks like a date may be stored as text, especially if it was imported or entered with a date order Excel does not recognize. Text dates can cause a subtraction error or an incorrect result. Convert or re-enter them as real Excel dates, and check that the cell aligns and formats like other dates in the workbook.

Formatting the answer as a date: Excel may display a small day count as a date because the result cell inherited a date format. Set the result cell to General or Number instead of changing the formula.

Using the wrong workweek: NETWORKDAYS assumes Saturday and Sunday are weekends. If your workweek differs, do not treat this result as accurate for your schedule; use the appropriate weekend-pattern option in the function or check the workday rules for your version of Excel.

Reversing the date order: If the end date is earlier than the start date, subtraction returns a negative number. That can be useful for identifying reversed inputs, but it is not a positive duration. Correct the dates rather than hiding the negative result.

Tips

Use descriptive column headings such as Start date, End date, Elapsed days, and Workdays. That makes it easier for someone else to understand why two columns of day counts might differ. Keep the input dates in date format and the calculated counts in General or Number format.

For repeated calculations, turn your data range into an Excel table by selecting it and choosing Insert > Table. A formula entered in a table column can fill down automatically as you add rows. Still verify that each row’s start and end dates belong together, and that the holiday list covers the dates in your calculations.

To enter a fixed current date quickly, Windows Excel supports Ctrl+; (Control and semicolon). On Mac, the equivalent is Control+; in many Excel configurations; keyboard mappings can vary. This inserts a date value that stays fixed, unlike TODAY, which recalculates. If the shortcut does not work with your keyboard layout, type the date directly.

Keep a small test interval nearby when building a larger worksheet. A Friday-to-Monday check reveals whether a formula is counting weekends; a same-day check shows whether your chosen method returns zero or one. These tests are simple ways to catch an off-by-one error before copying the formula across a report.

Troubleshooting

The formula displays as text instead of calculating: Make sure the cell is not formatted as Text. Change it to General, edit the cell, and press Enter again. Also check that the formula begins with an equals sign and that there is no apostrophe before it.

You see a number such as 46023 rather than a date: Excel stores dates as serial numbers. That is expected for a date-formatted value or a calculation that has not been formatted as a count. For a day-count result, select General or Number. For the input, use a date format if you want to see a readable date.

Subtraction returns an error: Inspect both inputs. If either date is text, re-enter it in a format Excel recognizes for your regional settings, or use Data > Text to Columns to convert imported text dates where appropriate. Confirm that the cells do not contain extra spaces or labels instead of dates.

The result is negative or unexpectedly large: Confirm the start date is earlier than the end date, and check the year as well as the month and day. A mistaken year or a swapped pair of dates can produce a plausible-looking but wrong result. For a changing TODAY calculation, verify that the starting date is not in the future unless a negative result is intended.

The workday count does not match a calendar: Check whether the calendar uses a different weekend pattern and whether the holiday list contains actual dates rather than text. NETWORKDAYS excludes Saturday and Sunday by default, but it cannot infer company closures or local holidays. Make the holiday range large enough and verify that the holiday dates fall within the interval.

Frequently Asked Questions

Does Excel count the first day when I subtract dates?
No. Subtracting a start date from an end date gives elapsed days between them. Add one when your definition requires counting both endpoint dates.

How do I count every calendar day, including weekends?
Subtract the start date from the end date for elapsed calendar days. Add one for an inclusive count. Neither method removes weekends.

How do I count only Monday through Friday?
Use NETWORKDAYS with the start and end dates. Add a holiday-date range to its arguments when you also need to exclude holidays.

Why does the same-day count show zero?
Ordinary subtraction measures elapsed time, so there are zero days between a date and itself. An inclusive count treats that single date as one calendar day.

Can Excel count days from a date to today automatically?
Yes. Use TODAY as the current-date endpoint in a subtraction. The answer updates when Excel recalculates, so use a typed fixed date instead if the result must not change later.

Do these formulas work in Excel for Mac or the web?
The date subtraction, TODAY, and NETWORKDAYS formulas work in current Excel for Mac and Excel for the web. Ribbon locations and keyboard shortcuts can differ, but the formulas themselves use the same syntax.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.