Function Problems

How to Count Dates Within 30 Days of Each Other in Excel

Anum Mushtaq Oct 21, 2026 868 views

Question details

The user needs a formula to count how many dates in a dataset fall within a 30-day range of one another.

How to Count Dates Within 30 Days of Each Other in Excel

Product: Excel
Device & OS: not provided
Scenario: Identifying the largest cluster of dates within a 30-day window from a list of varying dates.
Observed behavior: The user wants to use each date as a dynamic starting point to count adjacent dates within a 30-day span, rather than measuring against a single fixed date.

Before you start

Ensure your data is formatted as valid dates in Excel and that there are no blank cells interrupting your data range.

Solution 1Recommended

Use SUM and SORT Functions to Compare Adjacent Dates

This method compares adjacent sorted dates in an array to count those falling within the 30-day threshold.

By dynamically sorting the date column and comparing it to an offset version of itself, you can easily calculate the absolute difference between adjacent dates.

Select a target cell

Click on the blank cell where you want the final count to appear.

Enter the formula

Type =SUM(1*(ABS(SORT(A2:A5,1,1)-SORT(A3:A6,1,1))<=30)) into the formula bar. Replace A2:A5 with your actual date range, and A3:A6 with the range offset by exactly one row.

Apply the formula

Press Enter to calculate the number of dates that are within 30 days of the adjacent sorted date.

Use SUM and SORT Functions to Compare Adjacent Dates

Array Formulas: Depending on your Excel version, you may need to press Ctrl + Shift + Enter to evaluate array formulas correctly.

Solution 2

Use MAX and COUNTIFS Functions for Row Data

This alternative approach checks a row of dates and identifies the maximum count of dates that fall within a 30-day window (-15 to +15 days) of each date.

Count Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, SORT, and COUNTIFS, allowing you to easily manage and analyze date clusters without compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates.
2. Select a Target Cell: Click the cell where you want to display the calculation result.
3. Input the Formula: Type your preferred formula (e.g., the SUM and SORT combination) into the formula bar.
4. Press Enter: Hit Enter to instantly calculate the maximum cluster of dates within a 30-day span.

Fully compatible with Microsoft Excel formulas and date formatsFree and lightweight spreadsheet alternativeSupports advanced dynamic arrays and sorting functions

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date formula return an error?

This usually happens if the cells are formatted as text instead of dates. Select your data, right-click, choose 'Format Cells', and apply a standard Date format.

Can I change the 30-day window to 60 days?

Yes, simply change the '<=30' in the SUM formula to '<=60', or adjust the '+15' and '-15' in the COUNTIFS formula to '+30' and '-30'.

Do these formulas work with dates across different years?

Yes, because Excel and WPS Spreadsheet store dates as sequential serial numbers, the formulas accurately calculate the difference in days regardless of the year.

Excel & Data Problems