Does Excel consider date column as text if the number of blank values are more than the date values?

[ameyad09]

I have a file which has 2 date columns

first column has more blank values than date values due to which the entire column is shown as text when I filter on it.I tried using Format cells ,Datevalue,text(Field,'Date Format') and changed column datatype to date but it still keeps showing as text.

2nd Date column has more date values and it shows as Date when filtered on.

How can this issue be resolved for the first column without using Find and Replace manually?

I have uploaded the file at below link :Excel Link

Also,it does not have any viruses .Please take a look.

[#2]

@ameyad09 

There is absolutely no problem.

The column A Number format is set as custom which you need to change to date.

Select the entire A column (by clicking on A) and change its format to DATE.

That's all.

[#3]

@rprsridhar Hi, I have converted the column to Date but the filters still show Text Filters as below : 

I want to view the Date Filters here.

[#4]

@ameyad09 

Select the last option from the filter.

It will hide all the rows with date (in column A).

Select all the row which is showing - (only column A).

Then press delete button.

Then check. Instead of text filter it will show date filters.

I have checked it with the files you attached. Then it is working.

Cheers.

[#5]

Hi,Thank you for checking.However if I delete those values the column B & C data is gone as well which is required.
Is there any way to show the column A as Date without having to delete or Find and replace those hyphen values?

[#6]

@ameyad09 

Make sure you are highlighting column A only while deleting.

So that data from column B and C does not get deleted.

It looks like you have imported data from some other source / file.

Some thing has gone wrong there while importing.

I tried what you are asking for but could not succeed.

[#7]

@ameyad09 

As variant you may select entire column A, Ctrl+H and Replace All dashes on zero with matching entire cell content. Apply filter again, it'll Date filter. And 0 will be shown as "-" with your custom number format.