text filters vs date filters

[Artboy34]

I have two Excel workbooks, one being a clone of the other, and each having multiple columns in which dates are populated by formulas. In my original workbook, I am able to filter each column using date filters (next month, this month, etc.), but in the second workbook, the only filter option is a text filter. The formulas are the same, and the cells are formatted as dates in both workbooks. What am I missing?

I searched other forums, and even tried a workaround that someone suggested using the text-to-columns data tool, but it did not work.

Any suggestions/solutions would be greatly appreciated.

 

[FDibbins]

What is the formula you are using?

If you only have a text filter option, then your dates are not real dates, but text looking like dates

 

[Artboy34]

What is the formula you are using?

If you only have a text filter option, then your dates are not real dates, but text looking like dates

Hi Ford;

It doesn't appear that the formulas are the problem, since they are working on the one document (meaning the date filter appears as an option in all of the columns) but not on the other. In the document in question, there are two columns (C and L) in which the dates are entered manually. The dates in the other columns are populated through formulas based on one of those two columns:

=IF(C3="","", C3+60)

=IF(L3="","", EDATE($L3, -9))

What doesn't make sense to me is that columns in the original document containing either of these formulas (with variants for determining quarterly report dates, for example) allow the dates to be filtered as actual dates, but they can only be filtered as text — using the exact same formulas— in the second (copy) document.

What am I missing?

- Marty

 

[gvillepa]

​
+1

 

[Artboy34]

Forgot to add that all of the cells in question are formatted as dates in both documents.

 

[FDibbins]

Other than TEXT formatting has no affect on the actual contents of a cell, if a cell already contains text (even if it looks like a date or number), changing to Date format will not make the contents change to a date.

What exactly is in C3 and L3, show the actually contents (entry or formula), please?

 

[Artboy34]

Other than TEXT formatting has no affect on the actual contents of a cell, if a cell already contains text (even if it looks like a date or number), changing to Date format will not make the contents change to a date.

What exactly is in C3 and L3, show the actually contents (entry or formula), please?

The dates in both are entered manually in the MM/DD/YY format (06/30/15, for example).

 

[FDibbins]

OK last shot

do a quick test for me
=isnumber(C3)
=isnumber(L3)

if either come back as FALSE, that shows text, not number

 

[Artboy34]

Sorry to appear stupid, but where am I entering that information?

 

[FDibbins]

no, not stupid at all, and apologies for the delayed reply. Put that in an empty cell, it is just for a test, you will be removing it once we have established the actual format of those cells