Excel Date Count Formula: Count Days and Dates

Formulas & functions · Updated November 18, 2026

If your dates are real Excel date values in A2:A100, use =COUNT(A2:A100) to count the cells containing dates. If you mean the number of calendar days between a start date in A2 and an end date in B2, subtract the start from the end: =B2-A2.

What “count dates” can mean

There is not just one Excel date count formula, because “count dates” can describe several different jobs. You might want to count how many date entries are in a column, how many entries match a particular date, how many fall within a period, or how many days lie between two dates. You might instead need working days or the number of different dates represented in a list. Choosing the formula that matches the question prevents common off-by-one errors.

Excel stores valid dates as numbers and displays them using a date format. That is why ordinary counting functions can count dates without a special date-count function. The displayed value, such as 10/03/2026, is a formatted view of the underlying value. Formatting a cell to look like a date does not, by itself, turn text into a real Excel date.

Count cells that contain dates

With date-only values in A2:A7, use =COUNT(A2:A7). For this example, suppose the six cells contain October 1, October 3, October 3, October 7, October 12, and October 20, 2026. The result is 6, because all six are numeric date values. COUNT is usually the simplest choice when the range is meant to contain dates and nothing else.

COUNT counts numbers, not dates specifically. If the same range also contains a quantity or another number, that number is counted too. It ignores text and blank cells, including dates that were imported or entered as text. If the column may contain unrelated numbers, use a condition that defines the date range or clean the data before counting.

To count every nonempty cell instead, including text entries, use COUNTA—but only if every nonempty cell represents a date record. With dates in A2:A7 and no other types of entries, the formula is =COUNTA(A2:A7), and the example result is 6. COUNTA can also count text notes, so it is not a substitute for COUNT when the range is mixed.

Count a particular date or dates after a cutoff

With the six example dates in A2:A7, count entries equal to October 3, 2026, using =COUNTIF(A2:A7,DATE(2026,10,3)). It returns 2 because October 3 occurs twice. DATE constructs a date value from year, month, and day, avoiding ambiguity between regional date formats such as month/day/year and day/month/year.

With dates in A2:A7, count dates on or after October 7, 2026, using =COUNTIF(A2:A7,">="&DATE(2026,10,7)). The result is 3: October 7, October 12, and October 20. COUNTIF takes a range and a condition; when the condition includes a comparison operator and a calculated date, join the operator text to the date value with the ampersand.

For a moving cutoff, you can refer to a cell rather than put a fixed date in the formula. With dates in A2:A100 and the cutoff date in D1, use =COUNTIF(A2:A100,">="&D1). Changing D1 updates the count. Make sure D1 contains a real date value, not text that merely looks like one.

Count dates within a start and end date

With dates in A2:A7, count entries from October 1 through October 10, 2026, including both endpoints, using =COUNTIFS(A2:A7,">="&DATE(2026,10,1),A2:A7,"<="&DATE(2026,10,10)). It returns 4 for the example list: October 1, both October 3 entries, and October 7. COUNTIFS applies both conditions to the same range, so a date must meet the lower and upper limit to count.

For a reusable report, put the start date in D1 and the end date in E1. With dates in A2:A100, use =COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1). This counts an inclusive date interval. If D1 is later than E1, the result will normally be zero; check that the endpoints are in the intended order.

Handle dates that include times

A date-time value contains both a date and a time. For example, two entries displayed as October 3 may actually be October 3 at 9:00 a.m. and October 3 at 4:30 p.m. An equality test against midnight on October 3 will not match those later times, even if the cells are formatted to show only the date.

With date-time values in A2:A100, count every entry on October 3, 2026, by including the start of that day and excluding the start of the next day: =COUNTIFS(A2:A100,">="&DATE(2026,10,3),A2:A100,"<"&DATE(2026,10,4)). The upper boundary is exclusive, so times throughout October 3 are included without accidentally including midnight at the start of October 4. This approach is also useful for timestamped logs.

Count calendar days between two dates

With a start date in A2 and an end date in B2, =B2-A2 returns the elapsed number of days between them. If A2 is November 2, 2026, and B2 is November 6, 2026, the result is 4. This counts the day-boundary difference, not both named dates as separate calendar days.

If the question is how many calendar dates are included when both the first and last date count, add one. With the same layout, use =B2-A2+1; November 2 through November 6 inclusive is 5 calendar days. This distinction matters for trip lengths, reporting windows, and schedules. Confirm which convention is wanted before using the result in a calculation.

If the end date is earlier than the start date, subtraction returns a negative number. That can be useful for measuring direction, but if negative durations are not meaningful in your sheet, check the input order or add a validation rule. Both cells must contain valid Excel dates for subtraction to represent days.

Count working days, excluding weekends

For many schedules, the desired count is weekdays rather than all calendar days. With a start date in A2 and an end date in B2, use =NETWORKDAYS(A2,B2) to count Monday through Friday, including both endpoints. For November 2 through November 6, 2026, it returns 5 because those dates are Monday through Friday.

With the same start and end dates in A2 and B2 and holiday dates in D2:D5, use =NETWORKDAYS(A2,B2,D2:D5) to exclude listed holidays as well as weekends. If none of the holidays falls within the interval, the example result remains 5; a holiday on one of those weekdays reduces the count to 4. The holiday cells need to be real date values. NETWORKDAYS uses the standard Saturday-and-Sunday weekend; schedules with different weekend days can use NETWORKDAYS.INTL and its weekend argument.

NETWORKDAYS counts the workdays in an interval; it does not count how many date records appear in a list. Use COUNT or COUNTIFS for a list of logged dates, and NETWORKDAYS when calculating the length of a work period.

Count distinct dates rather than rows

A list can contain repeated dates, so the number of date entries may be larger than the number of unique dates. With date-only values and no blanks in A2:A7, use =COUNTA(UNIQUE(A2:A7)). The example list has five distinct dates because October 3 appears twice while the other four dates appear once. UNIQUE returns the different values, and COUNTA counts them.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. If the range includes blanks or other values, decide whether those should be treated as distinct entries before relying on this simple formula; filter or clean the source data to match the intended definition. For a large report, a PivotTable can also group repeated date values so you can inspect the distinct dates rather than only seeing a total.

Common reasons a date count is wrong

  • Dates are stored as text. COUNT ignores text, even if it looks like a date. Check whether the values are recognized as dates, then convert or re-enter text values as real dates. In Excel for Microsoft 365 on Windows, Data > Text to Columns can help convert consistently formatted imported values; choose the correct date order when the wizard asks for it.
  • The cells contain times. A date displayed without a time may still have a time component. Use a lower-inclusive, next-day-exclusive test when counting an entire calendar day.
  • The range includes other numbers. COUNT counts all numeric values in its range. Narrow the range or use criteria that match the dates you intend to count.
  • The date format is ambiguous. A value such as 04/05/2026 can be interpreted differently depending on regional settings. DATE(year,month,day) or a cell reference to a verified date avoids relying on the displayed order.
  • The result cell is formatted unexpectedly. A date subtraction should usually display as a number. If the result cell has a date format, Excel may show a calendar date instead of the day count; set the result cell to General or Number.
  • The selected interval has the wrong endpoints. Decide whether the start and end dates are inclusive. For date records, criteria using greater-than-or-equal and less-than-or-equal include both; a timestamp day count usually needs a less-than test against the following day.

Choose the formula that fits the question

Before writing a formula, identify what one row represents and what the result should mean. If each row is one date record, count entries. If the question names a specific day or reporting period, apply criteria. If it asks for a duration, subtract dates or count working days. If duplicates should count once, count distinct values.

  • How many numeric date entries are present? Use COUNT on the date column.
  • How many records match a date or cutoff? Use COUNTIF.
  • How many records fall within two boundaries? Use COUNTIFS, with inclusive or exclusive boundaries chosen deliberately.
  • How many days are between two dates? Subtract the start from the end; add one only for an inclusive calendar-date count.
  • How many weekdays are in the interval? Use NETWORKDAYS, optionally providing a holiday range.
  • How many different dates occur? Use UNIQUE with a counting function in a supported Excel version, or summarize repeated dates in a PivotTable.

The formulas here use commas between arguments, as in the standard English-language examples for Excel on Windows. Excel’s argument separator can depend on regional settings; some installations require semicolons instead. The functions described are available in Microsoft 365 for Windows, and the basic date-counting formulas also work in Microsoft 365 for Mac and Excel for the web. The exact menus and regional separators can differ, but the date logic is the same.

Frequently Asked Questions

What is the simplest Excel formula to count dates? If the cells contain real dates and no unrelated numbers, COUNT is the simplest option. It counts numeric date values and ignores blanks and text.

Why does COUNT return zero for dates? The apparent dates may be text rather than Excel date values. Convert them to actual dates, and check for imported spaces or inconsistent date formats before counting again.

How do I count dates between two dates, including both? Use COUNTIFS with a greater-than-or-equal condition for the start and a less-than-or-equal condition for the end. If the cells contain times, use the start of the first day and the start of the following day as an exclusive upper limit.

Does Excel count the start and end dates when subtracting? No. End date minus start date returns elapsed days between the dates. Add one when you need the number of calendar dates in an interval that includes both endpoints.

Can I count weekdays and leave out holidays? Yes. NETWORKDAYS counts weekdays between two dates, and its optional holiday range excludes listed holidays. Supply valid date values in that range.

How do I count each date only once? In Microsoft 365 or Excel 2021 and later, UNIQUE can return distinct date values and COUNTA can count them. Make sure blanks and non-date values are handled according to your reporting rules.

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.