Excel’s filter feature does not turn numbers into dates; it either shows existing cell values or returns values whose display is controlled by the cells’ number format. If filtered results look like dates, check the number format first: changing it to General or Number usually reveals the underlying numbers without changing the data.
What It Means When Filtered Numbers Look Like Dates
A number such as 45292 can appear as a calendar date if the cell is formatted as a date. Excel stores dates as serial numbers, so applying a date format changes how a numeric value is displayed, not the value itself. A date-looking result is therefore often a formatting issue rather than evidence that filtering has altered your data.
The word “filter” can refer to two different Excel features. The ordinary AutoFilter, available from the Data tab or a table’s header arrows, hides rows that do not match criteria. The FILTER worksheet function, available in Microsoft 365 and Excel 2021 or later, creates a separate, dynamic result range. Neither feature inherently converts numbers into dates, but the two situations involve different cells and can call for different fixes.
- Ordinary filter: The original cells remain in place; filtering changes which rows are visible.
- FILTER function: Excel returns values in a spill range, and the output cells’ formatting determines how those values appear.
How Excel Stores Dates and Numbers
In a standard Excel workbook using the 1900 date system, dates are represented by serial numbers: whole-number days counted from Excel’s date-system starting point. For example, the serial number 45292 corresponds to January 1, 2024, in that system. A time is stored as a fraction of a day, so a value can contain both a date and a time. Applying a date format can hide the time portion, while a time format can show it.
Excel does not give a date a completely separate underlying data type from a number. The stored value, the cell’s number format, and any formula that produced it all matter. A cell containing 45292 with General formatting displays 45292; the same value with a date format displays a date. Formatting does not change the serial number, so changing back to General or Number makes the number visible again.
There are two date systems used by Excel workbooks, 1900 and 1904. Their serial values differ for the same calendar date, by 1,462 days. This matters most when dates are copied between workbooks configured for different systems. It is not the usual reason a filter suddenly appears to return dates, but it can explain why copied date values are offset. You can check a workbook’s date system in File > Options > Advanced, under When calculating this workbook.
AutoFilter Versus the FILTER Function
With AutoFilter, a column’s values stay in their original cells. Choosing a filter criterion hides rows that do not meet it; clearing the filter makes those rows visible again. If a visible value looks like a date, inspect that cell’s format and value. The filter may simply have revealed a row that was already date-formatted, or a column may contain a mixture of formats.
The FILTER function works differently because its result appears in a spill range separate from the source data. The function returns values, not the source cells’ complete appearance. In particular, do not assume that a source cell’s number format will travel with a returned value. Format the output cells where the result spills. If the source contains dates but the output is General, the underlying serial numbers may appear instead of calendar dates; if the output is date-formatted, ordinary numbers may look like dates.
This distinction helps identify the cause quickly: if the original cells themselves change appearance as you filter, inspect their formats; if only a formula’s separate results look wrong, inspect the format of the spill area.
How to Diagnose the Displayed Value
Use a cell that looks wrong and check both its stored value and its formatting. These steps apply to Excel for Microsoft 365 on Windows:
- Select the cell that appears to contain a date.
- Look at the formula bar. For a plain value, it can show the underlying number even when the grid displays a date. If it shows a formula, note whether the result is numeric or text.
- On the Home tab, open the Number Format drop-down in the Number group. Check whether the selected cell is set to Date, a custom format, General, or another format.
- Choose General or Number to reveal a numeric value. If it changes from a date to a serial number, the value was numeric and the date appearance came from formatting.
- If the cell is part of a filtered table, clear the filter and compare nearby rows in the same column. Mixed formats can make similar values display differently.
You can also open Format Cells with Ctrl+1 on Windows and inspect the Number tab. On a Mac, Command+1 opens Format Cells in Excel; the ribbon’s Home tab also contains the Number Format controls. Menu wording and layout can vary slightly across Mac releases, but the key is to inspect the selected cell’s number format.
Fixing Numbers That Look Like Dates in a Filtered Table
If the values are meant to be ordinary numbers, select the affected cells and choose General or Number from Home > Number Format. If only one column is affected, select that column’s data cells rather than changing the entire worksheet. For a table, selecting its data cells is often safer than selecting an entire worksheet column, because it limits changes to the relevant records.
Changing the format does not alter the value, which is useful when a number was merely displayed incorrectly. However, if some entries are actual dates and others are quantities or identifiers, applying one format to the whole column may make the genuine dates look like serial numbers. Separate unlike data into different columns where possible, or select and format only the cells that should be numbers.
If the values are meant to be dates, keep or apply a date format instead. Select the date cells and choose a suitable option from Home > Number Format, or open Format Cells and select a date format. A format such as 14-Mar-2012 makes dates less ambiguous than a numeric month/day display, especially in workbooks shared across regions that use different date conventions.
Fixing a FILTER Formula’s Results
Suppose a worksheet has dates in A2:A100, descriptions in B2:B100, and a numeric flag in C2:C100, where 1 means a row should be included. With those columns and rows as the source layout, this formula returns columns A and B for rows flagged with 1:
=FILTER(A2:B100,C2:C100=1)
Enter the formula in an empty area with room for the results to spill. If the returned first column contains date serials, select the corresponding output cells and apply a date format. If the second column contains ordinary numbers that look like dates, format those output cells as General or Number instead. Format the relevant spill columns rather than blindly applying one format to the entire output.
FILTER is a dynamic-array function in Microsoft 365 and Excel 2021 or later. In Excel for the web, the function is available in supported Microsoft 365 accounts, though some ribbon details differ from the Windows desktop app. The formula’s result area must be unobstructed; an occupied cell in the spill range causes a #SPILL! error, which is separate from a date-format problem. Applying a filter formula does not copy source-cell styling, so set the result formatting where the formula outputs values.
Dates, Text, and Filtering Criteria
A value that looks like a date may be a true numeric date, a plain number with date formatting, or text that resembles a date. These cases behave differently. Numeric dates can be sorted and filtered chronologically when Excel recognizes them as date serials. Text dates may sort alphabetically or fail to match date-based criteria as expected, because Excel is treating the characters as text rather than as date values.
To check a suspicious entry, change its format to General. A numeric date typically reveals a serial number; text may remain visible as text. Formatting alone cannot reliably turn text into a genuine date value. If a column contains imported date text, use a deliberate conversion process and confirm the resulting dates before relying on date filters. Regional settings matter: for example, 03/04/2024 can mean March 4 or April 3 depending on the convention used when the text was created or interpreted.
One common misconception is that choosing a date filter converts numbers in the column into dates. A filter criterion only selects matching values; it does not repair mixed data or change the underlying type. If date filtering behaves unexpectedly, first check whether the entries are genuine dates, text, or unrelated numbers with date formatting.
Common Problems and Practical Checks
- Only some rows look like dates: The column may contain cells with different number formats. Select each affected group and apply a consistent format appropriate to its data.
- A formula result is a long number: The result may be a date serial displayed with General formatting. Apply a date format if the value represents a date.
- A quantity appears as a calendar day: Its cell or output range may have been formatted as Date. Change it to General or Number.
- A date filter misses entries: Some entries may be text, blank, or otherwise not stored as dates. Check a sample in the formula bar and with General formatting.
- Copied dates are offset: The workbooks may use different date systems. Check the 1900/1904 setting before changing values.
For a reliable dataset, keep one kind of information in each column, use consistent formats, and verify imported values before filtering. These habits make sorting, calculations, and date criteria more predictable; formatting can improve readability, but it cannot fix inconsistent underlying data.
Frequently Asked Questions
Why does Excel show a number as a date after filtering?
Filtering does not normally convert the number. The cell or formula output is likely formatted as a date, or the filtered row already had that format. Select the cell and change its format to General to check the underlying value.
How do I stop Excel from changing numbers to dates?
Set the relevant cells to General or Number before entering or pasting ordinary numeric values, and check the format of any FILTER formula’s output range. If the values are identifiers with leading zeros, use Text or another appropriate method because General may remove those zeros.
Does Excel FILTER copy the source formatting?
Do not rely on it to copy source-cell formatting. FILTER returns values into a spill range; format the result cells themselves as dates, numbers, or another suitable type.
How can I tell if a date is stored as a number?
Select the cell and change its number format to General. A genuine Excel date generally displays as a serial number. If it remains date-like text, it may be stored as text instead.
Will changing a date-formatted number to General erase the value?
No. Changing a number format changes how Excel displays the value, not the underlying numeric value. You can apply a date format again later if the number represents a date.
Related
- Excel Essential Keyboard Shortcuts for Everyday Work
- Excel European Number Format: Decimals and Separators
- Excel Subscript Out of Range: Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Number of Years From a Date, ExplainedTo calculate completed years from a date in Excel, use =DATEDIF(A2,TODAY(),"Y"), where A2 contains the starting date. For…
- Excel FIND Function: How to Find Text Within a StringIn Excel, use FIND to locate text inside a string and return the position where it begins. For…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- COUNTIF Between Dates in Excel: Formula and ExamplesTo count dates between two boundaries in Excel, use COUNTIFS with a lower-bound and an upper-bound condition. With…
- How to Sort Dates Correctly in Excel Without ErrorsTo sort dates correctly in Excel, make sure Excel recognizes the entries as real dates, then select a…
- Excel Filter Only Show Duplicates: A Practical GuideExcel does not have a built-in AutoFilter command that directly displays only duplicate values, but you can add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.