Forum Discussion
Date Field showing as text due to the '-' hyphens present in the field.
That typically happens when one or more cells do not contain a date (or have not been interpreted by Excel as being a valid date). To find them, select the column in question and press the F5 key on your keyboard (GoTo). Click the Special button. Select "Constants" (or "Formulas" if the cells contain formulas!) and only check the "Text" box. Now you can hit the Del key to empty those cells and you should be able to filter on dates.
Is there any way to handle the hyphens to show as date using any formula?
Thanks.
- Alternatively, do a Find and Replace and replace - with nothing (check the "Match entire cell contents" box)
- Hi,I tried this and it seems to work ,however as this data is exported via ,it will be a manual process.
Also, is this true that Excel consider date column as text if the number of blank values are more than the date values?
I tried using format cells to show as date but it still shows as Text.Rather than doing this manually each time, you are better off using a different method of pulling in the data. Have a look at the Data tab, there are options there to pull in your data from a plethora of different sources. Once you have your data in from the source it becomes very easy to record steps to do cleanup, like removing records which only contain a - or empty records. Finally you can set the data types of all columns as you need them and after that you can choose to push the results to a table on an Excel worksheet. If you create your pivot table using that new table as its source (or you can push the query result in a pivot directly!), you should never have an issue with dates again.