Fix Excel VBA AutoFilter Date Minus 14 Days Not Working
Question details
The user needs to filter a dataset using an Excel VBA AutoFilter to show rows with dates older than or equal to 14 days ago, but the standard date criteria calculation fails to filter correctly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing or running a VBA macro to automatically filter a column for records strictly 14 days or older from today's date.
- Observed behavior
- The AutoFilter fails to correctly recognize and filter the dates when using "<=" & (Date - 14) directly as a string criterion.
Ensure your target column actually contains valid date values (not text strings that look like dates) and save a backup of your workbook before running or modifying any VBA macros.
Use the CLng Function to Convert Date Criteria
Convert the calculated date into a long integer using the CLng function to ensure AutoFilter processes the date reliably regardless of regional formatting.
Spreadsheet software stores dates as sequential serial numbers. When passing date criteria to the AutoFilter method in VBA, string concatenation can sometimes misinterpret the date format (e.g., mixing up US and UK formats). Converting the date to a numeric value using `CLng` resolves this ambiguity and forces the filter to match the underlying serial numbers.
Open your workbook and press `Alt + F11` on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, double-click the module or worksheet where your AutoFilter macro code is currently stored.
Modify your AutoFilter code to wrap the date calculation in the `CLng` function. Change your existing code to look like this: `Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:="<=" & CLng(Date - 14)`.
Save your code and press `F5` or return to the spreadsheet to run the macro. The target column will now properly hide rows with dates newer than 14 days ago.
Run Macros and Filter Dates Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros in its advanced versions, allowing you to automate complex date filtering tasks using the exact same code you would use in Microsoft Excel.
- 1. Install WPS Office: Download and install WPS Office, then open your macro-enabled `.xlsm` or `.xls` file in WPS Spreadsheet.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab and click 'Macros', or press `Alt + F11` to open the built-in VBA Editor.
- 3. Apply the Date Filter Code: Insert the `CLng(Date - 14)` criteria logic into your standard AutoFilter script.
- 4. Execute Automation: Run the macro to instantly and accurately filter your date columns without formatting errors.
Frequently Asked Questions
Why does VBA AutoFilter sometimes fail with standard date formats?
VBA generally defaults to the US date format (mm/dd/yyyy). If your system settings or spreadsheet cells use a different regional format (like dd/mm/yyyy), VBA might pass the wrong string to the AutoFilter, causing it to return zero results. Using `CLng` converts the date to a universal serial number, bypassing text formatting entirely.
Can I filter for dates exactly 14 days ago instead of older?
Yes. To filter for an exact date match 14 days prior to today, simply change the operator in your VBA code. Use `Criteria1:="=" & CLng(Date - 14)` instead of the less-than-or-equal-to symbol.
How do I filter a date range between 7 and 14 days ago using VBA?
You can apply two criteria within the same field. Set `Criteria1:="<=" & CLng(Date - 7)` and `Criteria2:">=" & CLng(Date - 14)`, and ensure you add the parameter `Operator:=xlAnd` to require both conditions to be met.
Will this CLng workaround apply if I migrate to WPS Spreadsheet?
Yes. WPS Spreadsheet handles dates as serial numbers exactly the same way Excel does. Any VBA macro utilizing `CLng` for AutoFilter criteria will execute seamlessly in WPS Office.
