You can use the following formula to count the number of date occurrences between a particular date range in Excel:
=SUMPRODUCT((A2:A12>=$E$1)*(A2:A12<=$E$2))
This particular formula counts the number of date occurrences in the range A2:A12 where the date is between the start date in cell E1 and the end date in cell E2.
The following example shows how to use this formula in practice.
Example: Count Number of Date Occurrences in Excel
Suppose we have the following dataset that contains information about the number of sales made on various dates by some retail store:
Suppose we would like to count the number of dates between 1/2/2023 and 2/2/2023.
We can type these start and end dates in cells E1 and E2, respectively, and then type the following formula into cell E3 to count the number of dates between this range:
=SUMPRODUCT((A2:A12>=$E$1)*(A2:A12<=$E$2))
The following screenshot shows how to use this formula in practice:
We can see that there are 4 dates in column A between 1/2/2023 and 2/2/2023.
We can manually verify this is correct by identifying the three dates:
- 1/4/2023
- 1/5/2023
- 1/7/2023
- 1/26/2023
Note that if we change the start or end date, the formula will automatically update to count the number of dates between the new range.
For example, suppose we change the end date to 3/2/2023:
We can see that there are 8 dates between 1/2/2023 and 3/2/2023.
Feel free to change the start and end dates to whatever dates you’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Count Unique Values Based on Date Range
Excel: Conditional Formatting If Date is Before Today
Excel: Calculate Days Between Date and Today
