Formatting Issues

Fix Excel FILTER Function Date and Time Formatting Issues

Partner Editor Oct 9, 2026 869 views

Question details

The user needs to keep original date and time formatting when using the Excel FILTER function, which currently outputs numerical values instead of dates.

How to Preserve Date and Time Formatting in Excel FILTER Results

Product: Excel 365
Device & OS: not provided
Scenario: Extracting subsets of data containing dates and times using the dynamic array FILTER function.
Observed behavior: Dates and times in the filtered results are displayed as raw serial numbers, which fails to preserve the source formatting and negatively impacts subsequent sorting or pivot-table grouping.

Before you start

Verify that the dates in your source data are genuine Excel date values and not text strings disguised as dates, as text values will cause sorting and pivot table errors regardless of cell formatting.

Solution 1Recommended

Apply Date and Time Number Formats to Destination Cells

Since dynamic array functions extract underlying values rather than cell properties, you must manually format the destination cells to display the serial numbers as dates.

Excel formulas, including the FILTER function, are designed to return only the raw data values from the source cells. Because Excel stores dates and times as sequential serial numbers (e.g., 44210), extracting them without copying the cell formatting will cause these underlying numbers to appear. The most direct solution is to reapply the format to the spill range.

Select the destination range

Highlight the entire range of cells where the FILTER function is spilling its results, including extra rows if the data is expected to expand.

Open the Format Cells dialog

Right-click the highlighted area and choose 'Format Cells' from the context menu, or simply press the 'Ctrl + 1' keyboard shortcut.

Choose the correct format

Navigate to the 'Number' tab, select 'Date' or 'Time' from the category list, pick your preferred regional format, and click 'OK' to apply it.

Apply Date and Time Number Formats to Destination Cells

Handling Expanding Arrays: To avoid manually formatting cells every time your FILTER results grow, you can apply the date format to the entire destination column instead of just the currently populated cells.

Solution 2

Convert Text Dates to Genuine Serial Values

If applying a date format to the filtered results does not change their appearance, or if pivot table grouping fails, your source dates might be stored as text and require conversion.

Free Microsoft Office alternative

Easily Filter and Format Complex Data with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet application that seamlessly handles complex functions, dynamic arrays like FILTER, and custom cell formatting to streamline your data analysis.

1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx file containing your source data.
2. Enter the FILTER formula: Type the FILTER formula into your destination cell to extract the desired records dynamically.
3. Format the results: Highlight the spilled array range, press Ctrl+1, and select your desired Date or Time format from the menu.

Fully compatible with Microsoft Excel .xlsx files and advanced formulas.Easily apply custom date and time formats with a clean, intuitive interface.Features dynamic array support to spill your filtered results automatically.Lightweight, fast, and completely free to use on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show numbers like 44210 instead of a date?

Excel fundamentally stores dates as sequential serial numbers so they can be used in mathematical calculations. By default, January 1, 1900, is serial number 1. When a function extracts a date without bringing over its formatting, you will see this underlying raw number.

Can I make the FILTER function pull cell colors or fonts?

No. Excel functions, including dynamic arrays like FILTER, are designed to retrieve only the underlying data values. They cannot extract or copy cell formatting such as background colors, bold fonts, borders, or custom number formats.

Why won't my filtered dates group properly in a PivotTable?

If your dates aren't grouping by months or years in a PivotTable, they are likely stored as text rather than genuine Excel date values in the source data. You must convert the original data to actual dates using the 'Text to Columns' tool or the DATEVALUE function before filtering.

How do I format an entire dynamic array automatically?

While conditional formatting can be applied using the spill reference symbol (e.g., =A1#), the easiest way to ensure newly filtered dates always format correctly is to select the entire column letter (e.g., Column D) and apply the date format to the whole column.

Document & File Issues