'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Count Dates Older Than 7, 14, or 30 Days in Excel
Question details
The user wants to count the number of dates in a dataset that are older than a specific number of days or fall within a recent rolling date range using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking aging items, monitoring overdue tasks, or analyzing data based on rolling timeframes like 7, 14, or 30 days.
- Observed behavior
- The user requires specific COUNTIF and COUNTIFS formulas incorporating the TODAY function to calculate dynamic date thresholds.
Ensure your date column is formatted as valid dates rather than plain text, and verify the exact cell range containing the dates you want to evaluate.
Count dates strictly older than a specific number of days
Use the COUNTIF function combined with the TODAY function to find dates that are older than your target number of days (e.g., 7, 14, or 30 days).
The TODAY() function dynamically retrieves the current system date. By subtracting a set number of days from TODAY(), you can create a rolling threshold to count past dates.
Click on an empty cell where you want the total count result to be displayed.
Type the formula =COUNTIF(H2:H149,"<"&TODAY()-7), replacing 'H2:H149' with the actual range containing your dates.
To check for 14 or 30 days instead of 7, simply change the number 7 in the formula to 14 or 30 (e.g., =COUNTIF(H2:H149,"<"&TODAY()-30)).
Press Enter to execute the formula. Excel will display the total number of dates that meet the criteria.
Count dates within a recent date range
Use the COUNTIFS function to count dates that occurred in the past but are no older than a specific number of days (e.g., within the last 7 days).
Easily Calculate Date Differences with WPS Office
WPS Spreadsheet offers full support for COUNTIF, COUNTIFS, and TODAY functions, allowing you to seamlessly analyze aging reports, track overdue dates, and manage timelines.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the dates you want to evaluate.
- 2. Apply the COUNTIF formula: Select an empty cell and enter the formula =COUNTIF(range,"<"&TODAY()-days).
- 3. Press Enter to execute: Hit Enter to instantly calculate the count of older dates.
- 4. Explore more functions: Navigate to the Formulas tab to easily insert other Date and Time functions for complex calculations.
Frequently Asked Questions
Why is my COUNTIF formula returning 0 for dates?
This usually happens if the dates in your range are formatted as text rather than actual date values. To fix this, select your date range, go to the Home tab, and change the number format to 'Short Date'. Alternatively, use the 'Text to Columns' feature to convert them into real dates.
Can I reference a cell for the number of days instead of typing it in the formula?
Yes. Instead of typing the number directly (e.g., -7), you can reference a cell that contains the number of days. If cell A1 contains the number 7, your formula would look like: =COUNTIF(H2:H149,"<"&TODAY()-A1).
How do I count dates that match exactly today's date?
To count cells that contain today's date only, use the formula without any greater/less than operators: =COUNTIF(H2:H149, TODAY()).
Do these formulas update automatically tomorrow?
Yes, because the TODAY() function is volatile, the formulas will automatically recalculate the date differences based on your computer's current system date whenever the workbook is opened or modified.
