Function Problems

How to Use Excel FILTER Formula Based Only on the Day of a Date

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to separate a dataset, such as products and quantities, into different tables based solely on the day portion of a given date (e.g., dates on or before the 15th versus dates after the 15th), ignoring the month and year.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering an existing data range by extracting only the numerical day from a date column to distribute records into two distinct categories based on whether the day falls in the first or second half of the month.
Observed behavior: The user needs a dynamic array formula that evaluates the date conditions and successfully spills the corresponding filtered multi-column results into adjacent empty cells without throwing errors.

Before you start

Verify that your date column is formatted as actual dates (not plain text), otherwise the DAY function will return a #VALUE! error when attempting to extract the day.

Solution 1Recommended

Use the FILTER and DAY Functions Together (Multi-Column Spill)

This is the most efficient and recommended method. By combining FILTER with the DAY function, you can evaluate the dates and return entire rows of data spanning multiple columns at once.

The FILTER function is a dynamic array function that extracts records based on criteria you define. By nesting the DAY function inside the criteria argument, Excel will only evaluate the day element (1-31) of your date references.

Identify the data and criteria ranges

Locate the range you want to extract (e.g., columns B and C containing Products and Quantities, B2:C6) and the date column (e.g., column A, A2:A6).

Filter records for the first half of the month

Select the target cell where you want the first table to start (e.g., E2) and enter `=FILTER(B2:C6,DAY(A2:A6)<=15,"Not Found")`.

Filter records for the second half of the month

Select the target cell for the second table (e.g., G2) and enter `=FILTER(B2:C6,DAY(A2:A6)>15,"Not Found")`.

Apply the formulas

Press Enter. The filtered arrays will automatically spill the matching products and quantities into the adjacent columns and rows.

Spill Range Requirement: Ensure the cells adjacent to and below your formula are completely empty. If there is existing data blocking the results, Excel will return a #SPILL! error.

Solution 2

Filter Single Columns Separately

If you do not want to extract all columns together, or you need to place the extracted columns far apart from each other, you can apply the formula to each column individually.

Filter Data Easily with WPS Office

Use WPS Spreadsheet to Handle Dynamic Arrays

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and DAY. You can instantly extract and analyze data without needing complex workarounds or legacy array formulas.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates, products, and quantities.
2. Select a target cell: Click on the empty cell where you want your new filtered table to start spilling.
3. Input the dynamic formula: Type `=FILTER(B2:C6,DAY(A2:A6)<=15,"Not Found")` to filter out records strictly by their day value.
4. View the extracted table: Press Enter to execute. The dataset will immediately spill into the adjacent rows and columns dynamically.

Fully compatible with Microsoft Excel formulas, formatting, and dynamic arrays.Seamlessly executes the FILTER and DAY functions to split your datasets quickly.Lightweight, lightning-fast performance, and completely free to use.

Frequently Asked Questions

Why is my FILTER formula returning a #CALC! error?

The #CALC! error occurs when the FILTER function finds no matching records for your condition and you have omitted the optional [if_empty] argument. Adding a string like "Not Found" as the third argument in your formula will resolve this.

Why does the DAY function return a #VALUE! error?

This happens if the dates in your source column are stored as text rather than valid date serial numbers. You can fix this by selecting the column, going to Data > Text to Columns, and clicking Finish to convert them into true dates.

Can I filter data by both the day and the month simultaneously?

Yes. You can combine multiple criteria using the asterisk (*) operator, which acts as an AND condition. For example, `=FILTER(B2:C6,(DAY(A2:A6)<=15)*(MONTH(A2:A6)=8),"Not Found")` will return records that are on or before the 15th AND in the month of August.

Excel & Data Problems